+ Reply to Thread
Results 1 to 8 of 8

convert 9 digit number into day and time

  1. #1
    Registered User
    Join Date
    12-09-2010
    Location
    NY, NY
    MS-Off Ver
    Excel 2007
    Posts
    8

    convert 9 digit number into day and time

    Hello....I'd appreciate some assistance (again). I have a 9-digit number that represents the GMT day and time (from the beginning of the year) that I'd like converted into the local day and time. So, the number, for example is 076003449 in cell A1. The first 3 digits represent how many days have passed from Jan 1 (076 = March 15, Jan 1 = 1 and this year was a leap year). The remaining digits represent GMT time (003449 = 12:34 AM GMT; I don't care about the seconds). And, I'd like the GMT time converted to Pacific time (12:34 AM = 4:34 PM Pacific the previous day - so, the local date now becomes 14 March). What I'd like to appear in cell B1, then, is 3/14/12 4:34 PM. The year is the current year; i.e., 2012. I can adjust for the new year. Many, many thanks in advance for your help!!!

  2. #2
    Valued Forum Contributor
    Join Date
    03-14-2012
    Location
    Arizona USA
    MS-Off Ver
    Excel 2000/2007
    Posts
    408

    Re: convert 9 digit number into day and time

    You have explained the left 3 digits. I do not understand the right 6 digits.

  3. #3
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: convert 9 digit number into day and time

    Just for confirmation, 76 days from Jan 01, 2012 is March 16, 2012 not March 15.
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  4. #4
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: convert 9 digit number into day and time

    This will get you the first half of the problem (the days):

    Please Login or Register  to view this content.
    If you could explain how you converted 003449 into 12:34 AM then we can try and finish this problem.

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: convert 9 digit number into day and time

    If the time is always a six character text string, then:
    Please Login or Register  to view this content.
    Ben Van Johnson

  6. #6
    Registered User
    Join Date
    12-09-2010
    Location
    NY, NY
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: convert 9 digit number into day and time

    This is getting close. This is what I put into one cell =DATEVALUE("1/1/2012")+LEFT(A1,2)+(TIME(MID(A1,1,2),MID(A1,3,2),MID(A1,5,2))) but, I need to subtract 5 hours......please advise.....and, thanks!!!!

  7. #7
    Registered User
    Join Date
    12-09-2010
    Location
    NY, NY
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: convert 9 digit number into day and time

    and, sorry I forgot to mention this, I need to change the date if subtract the time goes to the previous day.

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: convert 9 digit number into day and time

    Try this one:
    Please Login or Register  to view this content.
    Since 5 hours is 5/24 of a day

  9. #9
    Registered User
    Join Date
    12-09-2010
    Location
    NY, NY
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: convert 9 digit number into day and time

    Thank you. The problem is solved. [SOLVED]
    Last edited by cords; 03-17-2012 at 06:14 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1