How Can I subtract from time within a 24 hour clock?
 

Hi,

I'm very new to excel and trying to make myself a template with a timeline based on an Event and various times before the event.

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.

I'm using the current formula and the columns are formatted to time.

=D2-C3

Thanks for looking

Oggy

Hi Oggy

D2 = Actual Time: 01:00
C3 is the time you wish to subtract

D2: 01:00
C3: 1:30

There a few ways, here are a couple.

1: =MOD(D2-C3,1)
2: =(D2+1)-C3

Kevin

Cheers Kevin,

The first one worked a treat, I haven't got a clue what it means but managed to get the table working how I wanted.

Regards,

Oggy

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.)

Thanks for the info, I've still got a lot to learn. I'm beginning to see some really handy things I will be able to do with this.