Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Conversion- DATEVALUE- including minutes
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?? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Conversion- DATEVALUE- including minutes
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. The Mecca wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Conversion- DATEVALUE- including minutes
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!! "Dave Peterson" wrote: 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. The Mecca wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Conversion- DATEVALUE- including minutes
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? "Dave Peterson" wrote: 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. The Mecca wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Conversion | Excel Worksheet Functions | |||
insert date | Excel Worksheet Functions | |||
Date Conversion Problem | Excel Discussion (Misc queries) | |||
Date Format - Conversion | Excel Discussion (Misc queries) | |||
date conversion | Excel Worksheet Functions |