# Subtract dates d:hh:mm

1. ## Subtract dates d:hh:mm

Seems that when I subtract a future date from NOW() and format the cell d:hh:mm that the result is 1 day off. So if I subtract NOW() (today is 10/24) from 10/25/08 it returns a 1 for the d when I would expect it to be zero.

2. I get a zero -- if you format the cell with 25/10 in it as General what is the value you get ? or alternatively if you assume 25/10 is in B1 what does:

=MOD(B1,1)

return ?

3. Originally Posted by Allardin
Seems that when I subtract a future date from NOW() and format the cell d:hh:mm that the result is 1 day off. So if I subtract NOW() (today is 10/24) from 10/25/08 it returns a 1 for the d when I would expect it to be zero.
Using d:hh:mm format can be problematic.....

d will never be greater than 31, for a start, so it's not a good format for calculating time periods 32 days or longer. Also the result can change if you are using 1904 date system, sounds like you are given the results you get.

Check with

Tools > Options > Calculation

If you uncheck 1904 date system you should get zero as required but beware, any existing dates in your spreadsheet will change

Note: The reason this happens is because the d format is not really designed for handling date differences, it's just giving the day of the month (hence why it doesn't go higher than 31) counting from 1900 or 1904 depending on the date system.

But....."day 1" in 1904 date system starts a day (and 4 years) later....hence the discrepancy

4. What would be a way to subtract 2 dates and times and display the result as dd:hh:mm:ss
Thanks

5. How about something like the below ?

Where B1 = end date time, A1 start date time

=INT(B1-A1)&":"&TEXT(MOD(B1-A1,1),"hh:mm:ss")

(Untested...)

6. =B1-A1 works, just use a custom format of dd:hh:mm:ss

7. are you sure about that ?

set date 1 to be say 1st April and date 2 to be 3rd May -- what answer do you get ?

8. I was assuming OP would take DLL's warnings about dd not supporting more than 31 days into account. If such is not the case, yours works better.

9. Beyond the answers, I'm curious; why would you expect the difference in days between 10/25 and 10/24 to be zero? There *is* a 1 day difference between today and tomorrow, yes?

10. You would expect it to be 0 if using say:

(TODAY()+1)-NOW()

ie the larger date is just a date value (no time), the smaller contains both date (integer) and time (decimal).

NOW() (for me at 7:28PM UK) is 39745.83 (to 2 decimals) -- the .83 representing the time.
Tomorrow (just date) is 39746

39746-39745.83 < 1 thus no day has passed.

Earlier post suggest may be a side effect of 1904 system (not tested myself but it does cause problems with most Date related functions) ... I thought possible that 2nd date set to have time value > time now()

11. Nice to see you here Luke, a little slow at MrExcel?

12. Hi there... just broadening my horizons... are you too a member on MrE ?
(I couldn't find a daddylonglegs... )

13. This is me

14. ah... good to know -- I'll take more care answering my date related posts from now on :-)

have a good w/end...

15. thanks DonkeyOte that works

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1