Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Apr 2003
    Location
    Kansas City, Missouri
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conversion of dates to decimals (Excel 2003 SP 2)

    I have a spreadsheet that is automatically generated. There is a column for the date, but it is in the awkward format of MMD:YYYY. I'm looking to try to change that in the final output.

    The problem is that when I export the data to Excel, my dates are no longer there. They are converted to decimal, such as:
    0.233634259
    0.168356481
    0.253078704
    0.253078704
    0.195439815

    I had hoped that changing the format to dates would work, but it didn't. It converts each value to 1/0/1900. Is there a way I can revert the number back to the date? Sure enough, when I type in 04:19:2006, it converts the value to 0.203078704. What is the colon doing?

    Sadly, I am generating an Excel sheet and don't have much say in the format. I may need to pursue other avenues if I cannot easily convert this number back to a date.

    Kevin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conversion of dates to decimals (Excel 2003 SP 2)

    The colon : is the time separator, so for example 4:19:2006 is interpreted as 4 hours, 19 minutes and 2006 seconds. Since 2006 seconds = 33 minutes and 26 seconds, you get 4 hours, (19+33) = 52 minutes and 26 seconds. This is 0.203078703... part of 1 day, the unit on which all dates and times are based in Windows/Excel. If all dates are in 2006, it might be possible to reconstruct the original dates, but if the dates can be from different years, that is impossible.
    If you cannot change the separator, can you force the column to be interpreted as text? You can then use formulas or VBA code to convert the text strings to valid dates.

  3. #3
    Star Lounger
    Join Date
    Apr 2003
    Location
    Kansas City, Missouri
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conversion of dates to decimals (Excel 2003 SP 2)

    Oh, of course! I was so locked into the date format, I didn't even think of the colon as an expression of time. Thanks, Hans, for removing my cranium from the smelly cavity.

    And yes, I see your point about how different years make the conversion back impossible. I'll be dealing with 2006 for a while, but the transition to 2007 could be messy as I'll have data from two different years.

    It seems I've learned all I can about the Excel side of things. With the knowledge of how Excel treats the colons, I must deal with the source program and see if I can replace the colons or export to Excel as text and not a number. I'm not sure how easy that'll be, but it's not longer of concern here.

    On a bright note, I am able to rearrange the order, so 2006:04:19 comes over as text just fine. I prefer that format anyway.

    Thanks for your insight.
    Kevin

  4. #4
    Star Lounger
    Join Date
    Apr 2003
    Location
    Kansas City, Missouri
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conversion of dates to decimals (Excel 2003 SP 2)

    Incidentally, assuming the year is 2006 for all of my dates, what formula would I use to convert 0.203078703703704 back to 04:19:2006? While I can pursue a solution for future data, I still need to do something with the data I do have.

    Kevin

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conversion of dates to decimals (Excel 2003 SP 2)

    Say that you have these numbers in column A, starting in A1.
    In B1 (or in any empty column), enter the formula

    =DATE(2006,HOUR(A1-TIME(0,0,2006)),MINUTE(A1-TIME(0,0,2006)))

    This will return a date value corresponding to the original date. You can fill down this formula as far as needed.

  6. #6
    Star Lounger
    Join Date
    Apr 2003
    Location
    Kansas City, Missouri
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conversion of dates to decimals (Excel 2003 SP 2)

    Splendid. Works like a charm.

    Thanks so much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •