Adding daily workhour results (1 Viewer)

BobNTN

Registered User.
Local time
Today, 11:12
Joined
Jan 23, 2008
Messages
308
Simple worksheet where:
Cell E6 is time in as in 7:28 AM cell formatted as h:mm
Cell F6 is time out as in 4:32 PM and the cell is also formatted as h:mm
Cell G6 is total using formula
=IF(F6<E6,F6+1,F6)-E6
That works fine giving 9:04
My Problem is when I try to add say five days of the results in column G, nothing I have tried gives correct total.
Is there a specific way to calculate these Hour:Min results?
BTW I have also tried formatting cell E and F as Time 1:30 and 13:30. Both work for the daily result. But the 5 day total still does not
Thanks in advance.

9:05​
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:12
Joined
Feb 19, 2013
Messages
16,553
time is stored as a decimal value. e.g. 43866.7094444444 The value before the decimal point is the number of days since 31/12/1899, the value after is the time expressed as number of seconds since midnight divided by 86400 (the number of seconds in a day).

So time cannot have a value greater than 0.9999999999 before rolling over to the next day

so 6 hours=0.25

say that is for 5 days =1.25 - but the time element is still only 0.25

so you need to get the total number of minutes by multiplying by the number of minutes in a day (1440)=1800 minutes

you can then convert this back to hours and minutes by dividing by 60 to get the number of full hours with the remainder being the number of minutes. The result will be a text value since you will be mixing numbers with non numeric characters

edit: note that formatting has no impact on the underlying value
 

BobNTN

Registered User.
Local time
Today, 11:12
Joined
Jan 23, 2008
Messages
308
So if I understand, if the result is 24 hrs or more, the result will only show the remainder OVER 24 hours. That is what it appears to do.
So how do I take a result that is 44 hours and 35 minutes and multiply only the hours by 60 and also add the minutes? Or any result as far as that goes?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:12
Joined
Feb 19, 2013
Messages
16,553
I explained how to do that. 44 hrs and 35 minutes is what you think you are seeing as a value - format the cell where this value is as a number, you will see a value 1.857638889 - which is 1 day plus 0.857638889 of a day. 0.857638889 of a day=20:35. A day has 24 hours, so 24 hours plus 20:35 hours/mins =44 hours 35 mins

multiply 1.857638889 by 1440=2675 minutes
=int(2675/60)=44 - gives you your hours
=int(mod(2675,60)) - gives you your minutes (rounded down)

you can concatenate these two values together if required, but it will be a text value which cannot be manipulated as a number
 

BobNTN

Registered User.
Local time
Today, 11:12
Joined
Jan 23, 2008
Messages
308
Pretty much works except 44 hours 55 minutes comes out as 44 hours 52 minutes.
And that total came from 9:05, 8:35, 9:05, 9:05, 905. 44 hrs 55 min so there should not be any rounding down of whole minutes.

Never mind. I got it. Was using wrong column in the minutes calc.

Thank you so much!
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 15:12
Joined
Feb 19, 2013
Messages
16,553
you are doing something wrong - don't forget you are dealing with decimal numbers so your calculation may be showing 9:05 hours, but there may be some seconds involved as well, you just don't see them because of the formatting. Suggest modify your formats to hh:mm:ss or numbers to verify - and that includes your source values in columns E and F

using this formula
=CONCATENATE(INT(K18*1440/60),"hrs ",INT(MOD(K18*1440,60)), "mins")
I get 44hrs 55mins

change K18 to whatever your formula points to (sum of 9:05, 8:35, 9:05, 9:05, 905

Capture.PNG
 

namliam

The Mailman - AWF VIP
Local time
Today, 16:12
Joined
Aug 11, 2003
Messages
11,696
Just a re-itteration of what has been mentioned, DO NOT use this result in any way shape or form other than "display" purposes.

It is utterly useless, if you want to i.e. build an invoice and multiply the number of hours with an hourly rate.
 

Users who are viewing this thread

Top Bottom