Excel spreadsheet and time?

[Funkie]

Hi all,

I've tried the various options for cells in an excel spreadsheet that I have created to duplicate my log book. However, whenever the hours are greater than 24, it resets and starts from zero - obviously for the next day....

Any idea's how I can over-rule this and display cumulative time?

Thanks in advance

F.

[Keef]

Excel stores date/time information as "days since 00 January 1900". If you subtract one from t'other, you get an answer in the same units. If you display it as date/time, then it appears as nonsense. If you try to add them when displayed in that way, then the total is even more nonsense.

Likewise if you just display time. That's because if you're displaying in hours and minutes, it will disregard whole days. Add 23:59 and 00:02 and you get 00:01 because that's what your clock face does.

However, if you display both as date time etc (however you prefer to see date and time), but calculate the difference in (say) hours, you can add them with no problem.

I made a little Excel sheet that shows how it works - it's here.

[R4+Z]

This has been covered before. See here

[Funkie]

That has worked a treat!

R4+Z,

Thanks for the link, the [h]:mm worked prefectly.

Question though, why do the [] make the difference?

Cheers.

[Tinstaafl]

Without the [ ] Excel thinks it's a 'normal' date/time ie 24 hrs in a day, so once 24 hrs rolls over it has to increment a day & restart the hours count. No different to a stopwatch: Once it goes past 9/10's of a second it increments by one second & the tenth's start again at zero. Ditto the seconds --> minutes etc.

The [ ] tells Excel not to increment the day once the number exceeds the increment value, but to continue to count using the same units.

[Funkie]

makes sense.

Cheers