Excel Formula

Topic

New Reply

I need a formula to put in a cell in Excel.
It is a time formula for tracking hours worked
first cell would be start time of shift say 21:30 hours and end time of shift would be 05:30 hours a 8 hour period. However some times the start is 23:27 an end is 02:26 hours and since most start stop times start in one 24 hour period and end in a different 24 hour period.
Any help would be appreciated
Thanks

Reply | Quote

Replies

Try this:

=(1 -StartTime)+StopTime

Reply | Quote

Hello
I tried that but it did not work but I thank you it seems to be on the right track.
In cell G42 I have the time of 23:00
In Cell G43 I have the time 07:00
I need Cell G44 to have a formula that computes the hours and minutes worked in this case 8 hours. In a case of some one working 7 hours and 15 minutes it should reflect 7.25 hours worked. Remember I am using a 24 clock for time and many of the workers work from one 24 hour period in to the next 24 hour period. as in from 21:30 to 05:30 hours
Again thanks for any help you can give.
It is truly appreciated

Reply | Quote

=(24 – StartTime)+EndTime seems to give the correct result but formatted as 07:15 not 7.25
I’ve just written some macro code to import workers times from a CSV, total them, add a tea-break allowance etc. Formatting the cells for correct display (i.e. hh:mm) just did not work for me – in frustration I ended up using everything as strings and extracting the values before and after the “:”
Still, I’ve got a working solution so I’m happy.

Reply | Quote

To subtract two time values with the result in hours and minutes: (8:30 – eight hours and thirty minutes)

=1-StartTime+EndTime
Format this cell as time

To subtract two time values with the result in hours and decimal fraction of hours (8.5 eight hours and thiry minutes):

=HOUR(1-StartTime+EndTime) +MINUTE(1-StartTime+EndTime)/60
Format this cell as Number.

Reply | Quote

CPOD:
This what I needed thanks for the help
I have installed your formula and every thing now works the way I need it to.

Reply | Quote

Thanks from me too.
I must play around with your suggestion.

Reply | Quote