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 It is hard to know what the problem is without seeing it. My suspicion is that there isn't a problem with the formula but in what you are expecting to get and what you are seeing. If you subtract 2020-09-11 5:00pm from 2020-09-12 11:00pm you might expect 18:00 hrs but see 1.25 instead. In excel dates are represented based on days so 1.25 means 1 1/4 days or 1 day 6 hrs. There are ways to force excel to display using a particular number (or date or time) format but you haven't really explained what you need to see and what you are seeing. Maybe include a sample sheet?
