Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Figuring 24 Hours Per Day
I am laying out a time sheet that has shop time, travel time, on site time,
break time, and travel time back to shop. I have this formula: =(IF(C8="","",(E8-D8)+(G8-F8)+(M8-L8)+(I8-H8)-(K8-J8))) It works great if you work during the day time, but if you work past midnight it won't figure the hours in I8 (start time) and H8 (end time). Can someone please help me out? Jeff |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Figuring 24 Hours Per Day
As I don't know what the rest of your data looks like, this is merely a best
guess. But if you're only concern is with I8 and H8, you could change that part of formula to: (I8-IF(H8<I8,H8+1,H8) This would add in the extra 24 hours needed. One thing though, why are you subtracting H8 (end time) from I8 (start time)? Wouldn't that result in a negative amount of time? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jeff" wrote: I am laying out a time sheet that has shop time, travel time, on site time, break time, and travel time back to shop. I have this formula: =(IF(C8="","",(E8-D8)+(G8-F8)+(M8-L8)+(I8-H8)-(K8-J8))) It works great if you work during the day time, but if you work past midnight it won't figure the hours in I8 (start time) and H8 (end time). Can someone please help me out? Jeff |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Figuring 24 Hours Per Day
The same formula but subtract (ending time - start time), and in cell format
select time 13:30. That should work. -- Alex *Remember to click "yes" if this post helped you. Thank you! "Jeff" wrote: I am laying out a time sheet that has shop time, travel time, on site time, break time, and travel time back to shop. I have this formula: =(IF(C8="","",(E8-D8)+(G8-F8)+(M8-L8)+(I8-H8)-(K8-J8))) It works great if you work during the day time, but if you work past midnight it won't figure the hours in I8 (start time) and H8 (end time). Can someone please help me out? Jeff |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Figuring 24 Hours Per Day
Example:
Start Time Ending Time Hours 8:00 1:00 17 9:00 16:00 7 10:00 13:00 3 11:00 12:00 1 and in hours this formula: =HOUR(IF(B21<A21,B21+24-A21,B21-A21)) the cell format to number. -- Alex *Remember to click "yes" if this post helped you. Thank you! "Jeff" wrote: I am laying out a time sheet that has shop time, travel time, on site time, break time, and travel time back to shop. I have this formula: =(IF(C8="","",(E8-D8)+(G8-F8)+(M8-L8)+(I8-H8)-(K8-J8))) It works great if you work during the day time, but if you work past midnight it won't figure the hours in I8 (start time) and H8 (end time). Can someone please help me out? Jeff |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Figuring 24 Hours Per Day
Here is a solution I used for a similar issue.
=IF(C8F8,((F8-E8+D8-C8)*24)+24,(F8-E8+D8-C8)*24) C is begin time F is end time D&E are lunch out and Lunch in The above formula tests if end time is less than begin time, and then adds 24 hours to the begin time if true. HTH "Jeff" wrote: I am laying out a time sheet that has shop time, travel time, on site time, break time, and travel time back to shop. I have this formula: =(IF(C8="","",(E8-D8)+(G8-F8)+(M8-L8)+(I8-H8)-(K8-J8))) It works great if you work during the day time, but if you work past midnight it won't figure the hours in I8 (start time) and H8 (end time). Can someone please help me out? Jeff |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Figuring 24 Hours Per Day
=(IF(C6="","",(E6-D6)+(G6-F6)+(M6-L6)+(IF(H6I6,(I6+24)-H6,I6-H6)-(K6-J6))))
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Figuring Late night Hours | Excel Discussion (Misc queries) | |||
Figuring out hours worked | Excel Discussion (Misc queries) | |||
Figuring cost based on hours | Excel Discussion (Misc queries) | |||
Figuring hours | Excel Worksheet Functions | |||
Figuring hours | Excel Worksheet Functions |