Forum Discussion
Calculating time between sheets
I agree with Riny_van_Eekelen
The safest option is to include the dates in the calculation of elapsed time
= (endDate+endTime) - (startDate+startTime) = MOD(endTime -startTime, 1)The second option is only usable if you are certain the elapsed time will never exceed 24hrs.
The number format you require is [h]:mm unless you choose to multiply by 24 and output the result as decimal hours, in which case it would be 0.0 "hours" .
The ranges defined for your dried weight calculations are incorrect and include the total itself, so giving rise to circular references.
Sockens I suspect you are looking at L21. You probably have the formula =L19-L17 there and you want to see 27:00 hours. In stead, you get 3:00. When you subtract dates and times, Excel defaults to a time format. Time can be presented from 00:00 up to 23:59 (=24 hours = 1 day). After that, time starts from 00:00 again. Hence, 27 hours becomes 3:00 , unless you explicitly tell Excel to continue adding hours. Time then becomes Duration. Use custom formatting like [h]:mm to display 27:00.