Time-Calculating amount

(Attached) I have done a lot of research, but have not seen an easy way to have a formula take into account the next day. I don't want to have to type in the date and the time. I need a formula (In the D column) that will take start and stop times and tell me how much time was spent. Many times will cross over until the next day. I can do military or regular time.


Thanks,
Jay

See attached. My formulas assume that you never cross midnight more than once.

Your second example is not the same as your first. I think you are trying to use the same times, but with a 12-hour format. But Excel is assuming that 6:00 is AM, not PM. You have to have the correct AM/PM in there for time arithmetic to work the way you want. I added an example using the same times as the first, but in 12-hour format.

An hour is 1/24th of a day; therefore, in Excel, hours are decimal values from 0.0000 to 0.9999.
Days are integer values 1,2,3,,,,
If your finish time is less than the start time, then you need to add 1 (day) to the finish time.
e.g.:
B5: Start @ 18:00 (Excel value: 0.7500)
C5: Finish @ 01:30 (Excel value: 0.06250)

Check if the finish is less than the start and add 1 (day) to the finish

Please Login or Register  to view this content.

Note: the expression (B5>C5) returns true (1) or false (0) as the multiplier for the 1 to be added. In this case true * 1=1 + 0.0625=1.0625
Subtract the start time 10.6250-0.75=0.31
If you format the cell as time, it will display 07:30
However, you should format as a number and multiply by 24 to get the duration in hours and not a clock time:
24*0.31 = 7.5 hours

Thus, in D5:

Please Login or Register  to view this content.

and formatted as number

Originally Posted by protonLeah

If your finish time is less than the start time, then you need to add 1 (day) to the finish time.

An alternative to the "add one day" route is to use MOD [again as per other examples we have to assume < 24 hr duration]

Please Login or Register  to view this content.