Forum Discussion
Time-Lapse Help
Hello,
I am trying to do a time-lapse formula and I get ###### that goes on forever (example below). How do I fix this (the employees don't know or use 24-hour times).
6 Replies
As suggested by Riny_van_Eekelen, MOD() with 1 as divisor will give you the time part of an elapsed period.
Multiply with 24 and you get the number of hours, being the same result but visualized with decimals instead of minutes. 54 minutes = 0,9 hours.
When you sum time you may pass full days and still want to view as hours.
Showing decimals does that and D3 is formated as h within brackets [h].mm to keep counting hours instead of showing like 2 days and 54 minutes.
- bosinander when I type in =MOD(B5-A5;1) I get the error message. How are you able to do you that without the error?
Perhaps in your locale it'll be
=MOD(B5-A5,1)It's better to copy formulae from file, not from post.
catherine9910 I suspect he end time is 12 hours after the start time, but it goes over midnight. You need to use a formula like:
=MOD(endtime-starttime,1)
That works for all time-lapses you want to calculate.
There is a negative time span, AM minus PM.
Excel does not show negative time as standard, and being elapsed time I assume that is not what you want.It may be visually shown by using a number format (Ctrl+1) like
hh:mm,"Negative not allowed"
- Right, when I added up the column, I got a negative number, so how do I fix it to where it is accurate? Or will we just have to do those manually?
