On Sat, 19 Dec 2009 13:40:31 -0500, Tim Neumark wrote: > 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] >> >>Follow the instructions immediately above me message, or below my signature block. Make sure your initiate the "unsubscription" action operation from the same address you used to subscribe.
-- Mark C. Miller, Indianapolis IN, USA --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
