Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 Quote:
|
#3
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.) |
#5
|
|||
|
|||
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.
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
add/sub time/hour tracking in 24 clock format | Excel Programming | |||
How do I enter time (12-hour clock) & have it display it? | Excel Discussion (Misc queries) | |||
counting cells within certain time frames - 24 hour clock | Excel Discussion (Misc queries) | |||
24 hour clock formula for elapsed time between 1215 and 1630 | Excel Worksheet Functions | |||
time diffrence with 24 hour clock | Excel Worksheet Functions |