1. ## Excel Formula

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

[img]/w3timages/icons/smile.gif[/img]

2. ## Re: Excel Formula

Try this:

=(1 -StartTime)+StopTime

3. ## Re: Excel Formula

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
It is truly appreciated

4. ## Re: Excel Formula

<font color=blue>=(24 - StartTime)+EndTime </font color=blue>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 [img]/w3timages/icons/crazy.gif[/img] 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.

5. ## Re: Excel Formula

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.

6. ## Re: Excel Formula

[img]/w3timages/icons/grin.gif[/img] 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.

7. ## Re: Excel Formula

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•