Before anyone says anything, I know I am being a wee bit dumb in asking the following. Can anyone clarify that I am correct in thinking that the formula to calculate hh:mm into working days is as below. Where a working day is 07:30 hours long?
SUM(A1÷7.5)
Cell 'A' being the hh:mm
Cell 'B' bing the answer
Moving for time to decimal is my concern. Not sure if it will give accurate results. Sorry if this sounds thick!
Thanks in advance.
"Times" are a difficult topic, so you are not alone in being puzzled. Numbers handles dates and times differently from Excel. Here, when you say "times" you probably mean a span of time (rather than a point in time). Numbers calls this Duration.
Duration can be displayed in a way that looks like a point in time (your hh:mm), which adds to the confusion:
But under the hood it is a different thing. To minimize ambiguity generally you are better off choosing the special Duration notation to display the value, something like this:
You can also use that notation to enter Duration values in cells. Subtracting one point in time value from another also results in Duration, as Ian demonstrates.
Another way to get your number of workdays from a Duration value in cell A1 would be to do something like this:
=A1/"7.5h"
If the word for hour in your language starts with something other than h then use that letter instead of h in the formula.
SG
"Times" are a difficult topic, so you are not alone in being puzzled. Numbers handles dates and times differently from Excel. Here, when you say "times" you probably mean a span of time (rather than a point in time). Numbers calls this Duration.
Duration can be displayed in a way that looks like a point in time (your hh:mm), which adds to the confusion:
But under the hood it is a different thing. To minimize ambiguity generally you are better off choosing the special Duration notation to display the value, something like this:
You can also use that notation to enter Duration values in cells. Subtracting one point in time value from another also results in Duration, as Ian demonstrates.
Another way to get your number of workdays from a Duration value in cell A1 would be to do something like this:
=A1/"7.5h"
If the word for hour in your language starts with something other than h then use that letter instead of h in the formula.
SG
Hi Glen,
The function DUR2HOURS is your friend.
Showing all the steps:
Formula in F2: DUR2HOURS(E2)
Formula in G2: F2÷7.5
Those are the steps. We can hide Columns E and F, or combine some steps into a single, complex formula. Your choice!
Regards,
Ian.
Converting timesheet times (hh:mm) to working days
