Excel - 24 time into absolute numbers or time *Tough One*

[Ketsu Fan]

Hi Eggheads,

I have a number in a cell, for example 1847. I want to convert this to either (1) time or (2) absolute numbers.

In this case, 1847 actually means 18 hours and 47 minutes, or 18:47 hours (06:47 PM). If 1847 was in cell A1, is there a way to convert it to 18:47 (time) in cell B1? I will even take hours in B1 and minutes in C1.

The other option I'm looking at is absolute figures. I want 12:00 AM on Jan 1, 2009 to mean 00 minutes. And I want 11:59 on Dec 31, 2009 to mean 525,600 minutes (or 527,040 minutes in a leap year). Thanks in Advance.

Cheers,
Ketsu

EggHeadCafe - Software Developer Portal of Choice
Five step of how to use logging utility in your projects using application blocks
http://www.eggheadcafe.com/tutorial...d-9acfdf92565e/five-step-of-how-to-use-l.aspx

 

[Claus Busch]

Hi Ketsu,

Am Mon, 26 Oct 2009 21:58:16 -0700 schrieb Ketsu Fan:

In this case, 1847 actually means 18 hours and 47 minutes, or 18:47 hours (06:47 PM). If 1847 was in cell A1, is there a way to convert it to 18:47 (time) in cell B1? I will even take hours in B1 and minutes in C1.

in B1: =TIME(INT(A1/100),MOD(A1,100),)

The other option I'm looking at is absolute figures. I want 12:00 AM on Jan 1, 2009 to mean 00 minutes. And I want 11:59 on Dec 31, 2009 to mean 525,600 minutes (or 527,040 minutes in a leap year). Thanks in Advance.

your Value in A1, then in B1:
=(A1-DATE(YEAR(A1),1,1))*1440


Mit freundlichen Grüssen
Claus Busch