Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    May 2013
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Coverting time zone and update date accordingly

    Hi everyone,

    can someone please help me with this. I have date in GMT format and I need to convert it to PST and adjust the date accordingly.

    This is how my data looks like and is all in date and time format.. I need result in D2.. Apreciate any help..

    Col A Col B Col C Col D
    Row 1 Date GMT Combine Date & Time (GMT) Combine Date & Time (PST)
    Row 2 May 29 2013 00:04:41 May 29 2013 00:04:41 ??

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Satin,

    If I read your requirement you should be able to do this: =A1-Time(8,0,0)
    This would go in B1. Since PST is 8 hours behind GMT you are subtracting 8 hours from the GMT.
    The Time function looks like this: Time(hours,minutes,seconds) HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Although it requires a bit more effort than RG's solution, the VBA equivalent is:

    Range("B1").Value = Range("A1").Value - TimeSerial(8, 0, 0)

    assuming the cells are formatted to something similar to mm/dd/yyyy hh:mm
    Last edited by Maudibe; 2013-05-30 at 22:49.

  4. #4
    New Lounger
    Join Date
    May 2013
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you everyone.. Rg, It did not work, gives me a #value error.. Maudibe, I am not very good at macros.. I entered the code you mentioned in the empty macro, it gives error "types mismatch error.."

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Both cells A1 and B1 are formatted as mm/dd/yyyy hh:mm:ss I added seconds as I just noticed you had them shown.
    Place in A1: 3/25/2013 6:00:00 run the following code and B1 will equal 3/24/2013 10:00:00 PM

    time.jpg

    Code:
    Public Sub test()
    Range("B1").Value = Range("A1").Value - TimeSerial(8, 0, 0)
    End Sub
    RG's formula is gold as usual. Here is a screen shot of his at work

    time2.jpg

    Try again
    Maud
    Attached Files Attached Files

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Quote Originally Posted by satin1 View Post
    Rg, It did not work, gives me a #value error..
    Satin,

    Seems to work fine for me.
    GMT.JPG
    Are you sure your dates are dates and not text?
    HTH
    Satin - GMT Example.xlsx
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    New Lounger
    Join Date
    May 2013
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    My original Date is in General format "Wed May 29 00:15:12 GMT 2013".. I am trimming and then concatinating the date and time. Then i change the format of combined date to mm/dd/yy hh:mm which also is giving me a "value" error.. I am sure there something minor that i am missing ..

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    As RG surmised, your dates are most likely text. if possible, start out with a date/time format then you can add/subtract/manipulate them all day long. Format the cell with the custom format [$-409]mmmm d, yyyy hh:mm:ss then use RG's formula in the cell you want adjusted or the code I had previously provided

    time3.jpg Time4.jpg


    Code:
    Public Sub test()
         Range("C5").Value = Range("B5").Value - TimeSerial(8, 0, 0)
    End Sub
    HTH,
    Maud

  9. #9
    New Lounger
    Join Date
    May 2013
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It worked thank you so much Rg and 3 star.. While concatinating, rather thant using concat function, I just used + sign and then chnaged the format to [$-409]mmmm d, yyyy hh:mm:ss.. and then it worked.. Thanks again..

Posting Permissions

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