1

Is there a way to start with a negative time (-24:00), add to it in h:mm format (+1:00) and get an answer (-23:00)?

1

2 Answers 2

1

Excel uses a weird time/date format and makes even stranger choices with times that have no date. All times without dates attached are entered as being from January 0, 1900, which is the beginning of the date line in Excel. Going backwards past midnight (by, say, subtracting 6 from 5am) results in an "error" and Excel will display the value as a string of pound signs (########).

To get around this, you can assign the time to a day further down the number line and change the formatting to show it only as a time. For example, 5am becomes January 1, 2013 at 5am. Then if you subtract hours (or days) from the value, Excel can properly keep track.

You can use the date functions to find out how many hours or days are between two values. There are many of them, but there is a guide available that covers how Excel handles dates and times, as well as all of the date and time functions here: The Definitive Guide to Using Dates and Times in Excel

1
  • Thanks Excel Tactics. Looks like I can't do what I want, the way I'd like. Plenty of options using different work-arounds. The "Definitive Guide to Using Dates and Times in Excel" is great for non-negative date and time calculations. Appreciate your helpful answer. Regards, Mick
    – Mick
    Jan 7, 2014 at 8:54
0

Yes but you need to change your Excel spreadsheet to use the "1904" date system. You can do that from menu File->Options->Advanced scrolling down to "When calculating this workbook" and select "Use 1904 date system".

Bearing in mind that there is no time such as "24:00:00" since time is measured from 0:00:00 to 23:59:59 ...

... You can then enter into a cell formatted as a time, a negative time like so:

= TIMEVALUE("23:59:59") * -1

then adding a value from another time-formatted cell, such as 1:00:00, would achieve what you want.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .