Hours Timesheet

[AD_Taylor]

I am using the formula:

=(INT(1/86400*U6) ) &" day(s), "& INT(MOD(U6, 86400)/3600) & " hour(s), " & INT(MOD(U6, 3600)/60) &" minutes, " & MOD(U6, 60) &" seconds"

to split a column that contains a cumulative total of seconds into days, hours, minutes and seconds.

This works perfectly when the number of seconds is positive, however, on this timesheet the number of seconds can be negative, for example if the user has worked under their allocated hours and needs to make the time up.

In this case the formula produces strange results such as showing the days as '-1' when there are in fact 0 days. There are still negative hours and minutes though.
Also the hours and minutes are calculating as if they were positive still, i.e. adding on 12 mins to 40 mins becomes 52 mins rather than the 28 it should be if the figures were negative.

Can anybody helps sort this out, please?

Thanks in advance,
Adam

 

[AD_Taylor]

Sorted!

Found out that when the numbers were going negative they were the inverse of what they should be.

For example -7 hours was coming out as 17 hours. Sorted it by doing 24 - [Number of Hours] = 7

Also on the days part I found that using the INT function on a negative number actually returns the wrong number. For example INT( -1.1122) = -2. By using the ABS() function in this as well, INT( ABS( -1.1122) ) = 1. Its alright for it to be positive here for me as I'm just looking for an actual number.

So in summary for anyone that wants to know (assuming the number of total seconds is in A1):

Days:

=IF( A1 >= 0,   (INT(1/86400*A1) ),  (INT(  ABS(  1/86400*A1 )   ) )     )

Hours:

=IF( A1>=0,   INT(MOD(A1, 86400)/3600), 24-INT(MOD(A1, 86400)/3600)  )

Minutes:

=IF(  A1 >= 0,  INT(MOD(A1, 3600)/60), 60 - INT(MOD(A1, 3600)/60) )

Seconds:

=IF( A1 >= 0,    MOD(A1, 60), 60 - MOD(A1, 60) )