2009/12/19 Brian Barker <b.m.bar...@btinternet.com>: > 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")
Thanks. That didn't work when characters set as Swedish (which is the default on my machine), since ” ” is the thousands delimiter. 15706 days shows up as 16 days… But of course I found a solution: =TEXT(INT(DAYS(B1;A1));"0")&" "&TEXT(DAYS(B1;A1)-INT(DAYS(B1;A1));"HH:MM") Thanks for the hint. I didn't know about the TEXT function, that you could specify a format code in it. Will probably be useful for me in the future. > >> "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. Yes, you are right. But it would be really nice if ”0 HH:MM” worked. It would be very elegant. Johnny Rosenberg > > Brian Barker --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
