How Can I subtract from time within a 24 hour clock?
"OggyAv8er" wrote:
I managed to achieve a working table but I have a problem as I work
through midnight. i.e. If the event time is 01:00 and I need to take
away 1:30, i want the table to display 23:30. at the moment it displays
-0:30.
It displays -0:30 only if you are using a Mac or you set the "1904 date
system" option.
If you did the latter, that is ill-advised.
Yes, it makes it easy to display negative time. But unless you set that
option consistently in __all__ of your workbooks, it is like to screw you up
eventually when you copy-and-paste dates across workbooks.
"OggyAv8er" wrote:
I'm using the current formula and the columns are formatted to time.
=D2-C3
The easiest way to do this is to include the date with the time.
You can still __format__ the cell to __display__ only the time of day. But
then you formula will work across all shifts, even greater than 24 hours.
(It would be prudent to format the cell as Custom [h]:mm instead of h:mm.
The [h] notation will display hours greater than 23.)
Alternatively, use one of the following formula:
=D2-C3+(C3D2)
or
=MOD(D2-C3,1)
formatted as Custom h:mm or, preferrably, [h]:mm.
(Even if hours will never exceed 23, it is a good habit to use [h]:mm
whenever you want to display __elapsed__ time, not time of day.)
|