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.
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.
Last edited by Allardin; 10-24-2008 at 08:59 PM.
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 ?
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
Last edited by daddylonglegs; 10-24-2008 at 07:45 AM.
What would be a way to subtract 2 dates and times and display the result as dd:hh:mm:ss
Thanks
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...)
=B1-A1 works, just use a custom format of dd:hh:mm:ss
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 ?
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.
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?
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()
Last edited by DonkeyOte; 10-24-2008 at 03:01 PM.
Nice to see you here Luke, a little slow at MrExcel?
Hi there... just broadening my horizons... are you too a member on MrE ?
(I couldn't find a daddylonglegs... )
This is me
ah... good to know -- I'll take more care answering my date related posts from now on :-)
have a good w/end...
thanks DonkeyOte that works
Last edited by Allardin; 10-24-2008 at 08:49 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks