Results 1 to 8 of 8
  1. #1
    CChambers
    Guest

    Re: converting a number into a date format

    While on the cell in question, right-click. Choose Format Cells. Go to the Number tab. Select the Date formatting. Then choose from the list at the right for how you want it displayed.

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: converting a number into a date format

    I know how to do Format Cells, but if you look at the way I explained the data, that will not work, especially when the year is reversed when the data is brought into Excel. If I click on that cell right now in the spreadsheet and try format dates, it thinks it's July 11th, 1927 when it should be 2/23/01. I've attached the file to this posting.
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    converting a (julian) number into a date format

    A user in a recent class proposed the following question to me. They have data that they are importing from another database system and it brings in the date in the following format, 10054, which she said is like a Julian format (which I'm not familiar with). 01 is the month. It's just reversed when it's brought in from their system. 054 is the 54th day of the year. So she said that 10 054 is really 2/23/01, the 54th day of the year.

    Is there an easy way to convert that number into the correct date 2/23/01?

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: converting a (julian) number into a date format

    Have a look at the <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q95/0/78.ASP>Microsoft's explanation of working with Julian dates</A>
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: converting a (julian) number into a date format

    Geoff,

    I appreciate the link to the article. I tried doing a search there before, but for some reason it didn't find Julian. Must have been the way I was searching....

    Will that solution really work in this case? The user said that the year number has been transposed in their system. (guess theirs is not really a tru Julian format?) When they see 10 for the year, it should really be 01. Is there a easy way to transpose numbers in Excel?

    -Kelley

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: converting a (julian) number into a date format

    Kelley,

    That link doesn't explain a transpoised year; it also only tells how to convert into Julian, not out of julian, as you require.

    This one might do it (assuming A1 has the date in Julian):
    =DATE(100+MID(A1,2,1)*10+MID(A1,1,1),1,1)+MID(A1,3 ,3)-1
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: converting a (julian) number into a date format

    If that is in A1, then the following formula will convert it to a date serial value:

    <pre>=DATE(LEFT(A1,1)+MID(A1,2,1)*10+2000,1,1)+RIG HT(A1,3)-1
    </pre>

    Legare Coleman

  8. #8
    Star Lounger
    Join Date
    Jan 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: converting a (julian) number into a date format

    This seemed to work! Thanks so much.

    -Kelley

Posting Permissions

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