Date Conversion- DATEVALUE- including minutes

[Guest]

Currently Excel stores dates as sequential serial numbers so they can be used
in calculations. By default, January 1, 1900 is serial number 1, and January
1, 2008 is serial number 39448 because it is 39,448 days after January 1,
1900.

My problem is that I have data that includes minutes- so 1/1/1900 at 12:00AM
is serial number 0 and 8/1/2006 4:00PM is serial number 56057580. Is there a
function that can convert this serial number back into a date and time
format??

 

[Dave Peterson]

If 56057580 were July 30, 2006 9:00 PM, then you could put that number in A1 and
use a formula like:

=a1/60/24

and give it a custom format of:
mm/dd/yyyy hh:mm:ss
(or whatever you like)

If it really represents 8/1/2006 4:00PM, you could use:
=A1/60/24+(43/24)

43/24 is the same as 1 day + 19 hours.

 

[Guest]

wow- you have NO idea how long it took me to get to this point. months,
literally. Someone gave me a number and a date and said- figure out how to
get this number to this date, without even knowing that it was minutes.
You are a life saver and now everyone will think I am a genius!! Thank you
Dave!!

 

[Guest]

unfortunately it does not work for all dates. When I used the dates for prior
years- I am 2 days off. When my date is 6/2/2002- and my serial number is
53870040 when using this formula is comes out to-6/4/02 1:00 PM

can you explain what logic behind the 43/24?