At 14:07 19/12/2009 +0100, Johnny Rosenberg wrote:
I have two dates and I want to calculate how many days, hours and minutes it is between them. The dates are 2009-12-22 16:00 and 2010-01-04 07:00, and as all of you already know it's 12 days and 15 hours between them. But since 12 means "12th day since 1899-12-30" the result of this calculation is 11 15:00 when formatted as "DD HH:MM".
Indeed: as you suggest, "DD" here refers to a date, not a number of days.
I didn't find a way to get around this by formatting only. For example "0 HH:MM" didn't work. Any suggestions?
No. In any case, since "DD" represents a date, it could never work for intervals of 32 days or more. My best effort is:
=TEXT(INT(DAYS(B1;A1));"00 ")&TEXT(DAYS(B1;A1)-INT(DAYS(B1;A1));"HH:MM")
"DD HH:MM" will probably work in Excel since 0 in Excel is 1899-12-31, which also means that Calc dates are the same as Excel dates 1900-03-01 - ?, but not before that date (Excel thinks that 1900 is a leap year).
Even so, an interval of 32 days would show as "1" (representing 1 February 1900) and so on.
Brian Barker --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
