time formula

[Guest]

I am trying to calculate in and out time for a time sheet spreadsheet.
Example: 9:00 am (in time), 5:00 pm (out time). I devised a formula:

=IF(D7<C7,((C7-D7)*24)+((C7+D7)*24)*2,(D7-C7)*24)

It works for all senarios except if you begin with PM and end with AM.
Example:
5:00 pm (in time), 1:00 am (out time)

Can anyone help me!!

 

[mmohon]

I've done something similar for Surgery in and out time for my
hospital. Our times never cross a day though.

Understand that time is saved as a faction of a day: a date/time raw
looks like 1000.0001
everything to the left of the decimal is date info, everything to the
right is time info. 1.0 is one day 1.5 is 1 day 12 hours etc

You could add an if statement like.....if ( end time < start time, end
time + 1 ) this adds 24 hours, so 7 am end time raw data would be 31
hours, minus 7pm start time would be 19 hours....so 31 hours minus 19
hours = 12 hours

hope that helps

 

[Ragdyer]

Start time in A1,
End time in B1,

*Either* of these should work:

=B1-A1+(B1<A1)*24

=MOD(B1-A1,1)*24

 

[Harald Staff]

=(D7-C7+(D7<C7))*24
assuming nobody works more than 23 hours 59 minutes.

HTH. Best wishes Harald

 

[Guest]

Thank you Harald, this worked great!!

=(D7-C7+(D7<C7))*24
assuming nobody works more than 23 hours 59 minutes.

HTH. Best wishes Harald

 

[Harald Staff]

Glad to hear that, thanks for the feedback. In case someone should ask,
+(D7<C7)
adds 24 hours to the end time if it's earlier than start time.

Best wishes Harald