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

[OggyAv8er]

Hi,

I'm very new to excel and trying to make myself a template with
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 wor
through midnight. i.e. If the event time is 01:00 and I need to tak
away 1:30, i want the table to display 23:30. at the moment it display
-0:30.

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

=D2-C3

Thanks for looking

Ogg

 

[Kevin@Radstock]

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

 

[joeu2004]

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.

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+(C3>D2)
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.)

 

[OggyAv8er]

Thanks for the info, I've still got a lot to learn. I'm beginning t

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 i displays
-0:30.-

It displays -0:30 only if you are using a Mac or you set the "1904 dat

system" option.

If you did the latter, that is ill-advised.

Yes, it makes it easy to display negative time. But unless you set tha

option consistently in __all__ of your workbooks, it is like to scre
you up
eventually when you copy-and-paste dates across workbooks.

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 2
hours.

(It would be prudent to format the cell as Custom [h]:mm instead o
h:mm.
The [h] notation will display hours greater than 23.)

Alternatively, use one of the following formula:

=D2-C3+(C3>D2)
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.