I have attempted to unsubscribe from this list a dozen times. Would someone please remove me?
On Sat, Dec 19, 2009 at 1:25 PM, Johnny Rosenberg <[email protected]>wrote: > 2009/12/19 Brian Barker <[email protected]>: > > 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: [email protected] > For additional commands, e-mail: [email protected] > >
