Page 2 of 2 FirstFirst 1 2
Results 11 to 18 of 18

Thread: Converting Text to Datetime Format and Then Converting From GMT to Local Time

  1. #11


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Quote Originally Posted by Hercules1946 View Post
    This was the bit I noticed when I was looking at the link I sent to you:

    "If the time zone that you want displayed is using daylight saving time, subtract an additional hour."
    I mean, that's obvious, right?

    I'm looking for a method that automatically does this rather than having to build the logic to figure out when to subtract different number of hours. I'm surprised that it doesn't exist natively in Excel. I'm hoping someone has built some sort of package that can be imported, since I assume this is a common need.

  2. #12
    Quote Originally Posted by Pecoflyer View Post
    Lots of information at http://www.cpearson.com/Excel/LocalAndGMTTimes.htm ( see table with VBA functions at the end)
    Thanks! This looks promising, although it looks like I'll have to dust off my extremely old VBA skills for this one.

  3. #13
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by jbayko View Post
    I mean, that's obvious, right?
    Yes it is, and I suppose that I was trying to make the point that there may be a simple solution based on the limited amount of information you have provided. Of course, if you can find an off the shelf solution, then that solves the problem. Pearson VBA is always very comprehensive, if sometimes a little over-elaborate, so best of luck with that

  4. #14
    Quote Originally Posted by Hercules1946 View Post
    Yes it is, and I suppose that I was trying to make the point that there may be a simple solution based on the limited amount of information you have provided. Of course, if you can find an off the shelf solution, then that solves the problem. Pearson VBA is always very comprehensive, if sometimes a little over-elaborate, so best of luck with that
    Got it. Thanks.

    This is something I'm going to be doing many many many many times with large amounts of data, so the simple solution isn't so easy in this case, and it's worth a bit of up front investment of time for me to figure out a more automated way.

  5. #15
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by jbayko View Post
    Got it. Thanks.

    This is something I'm going to be doing many many many many times with large amounts of data.
    This has no bearing on the complexity or otherwise of the processing. A simple process can handle repetition and high volumes of data faster than a complex one. I wasn't suggesting that you would have to remember to (manually) add one hour to each data entry! If you are dealing with a lot of information, then you are going to need an optimum solution. If you are wanting someone to help you with this, that person will need a lot more information than you have provided. Looking at this daylight saving issue, its always one hour, and the dates are definitive, otherwise my computer wouldn't be able to adjust its clock automatically. So.. maybe I could create a table of all the variations, and use an index or look up to make the correct formula adjustment? Maybe I would use some VBA.... I'd only know which way to go if I spent some time sitting at your desk!

  6. #16
    Quote Originally Posted by Hercules1946 View Post
    This has no bearing on the complexity or otherwise of the processing. A simple process can handle repetition and high volumes of data faster than a complex one. I wasn't suggesting that you would have to remember to (manually) add one hour to each data entry! If you are dealing with a lot of information, then you are going to need an optimum solution. If you are wanting someone to help you with this, that person will need a lot more information than you have provided. Looking at this daylight saving issue, its always one hour, and the dates are definitive, otherwise my computer wouldn't be able to adjust its clock automatically. So.. maybe I could create a table of all the variations, and use an index or look up to make the correct formula adjustment? Maybe I would use some VBA.... I'd only know which way to go if I spent some time sitting at your desk!
    Thanks for the advice.

    I was looking at a sample of the data converted to PDT and it wasn't making sense. I got confirmation from the data's source that it's already in PDT even though the data itself is marked at UTC...doh! It looks like I won't have to do anything at all.

  7. #17
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by jbayko View Post
    Thanks for the advice.

    I got confirmation from the data's source that it's already in PDT even though the data itself is marked at UTC
    I hope that our continued discussion has assisted you in finding this out. I presume your aware that a local time in (say) California can be expressed as 14:30 PDT or -0700 UTC and the latter would always be adjusted when PDT is in operation? This becomes -0800 UTC at other times. Interestingly, in Arizona, they currently have the same local time as California because Arizona doesn't operate daylight saving. Complicated or what?
    I'm glad to hear that you have taken a good step towards a solution!

  8. #18
    Quote Originally Posted by Hercules1946 View Post
    I hope that our continued discussion has assisted you in finding this out. I presume your aware that a local time in (say) California can be expressed as 14:30 PDT or -0700 UTC and the latter would always be adjusted when PDT is in operation? This becomes -0800 UTC at other times. Interestingly, in Arizona, they currently have the same local time as California because Arizona doesn't operate daylight saving. Complicated or what?
    I'm glad to hear that you have taken a good step towards a solution!
    Yes I'm aware of all that. All of the data was explicitly appended with "GMT+0000 (UTC)". One would presume this means the data is UTC, but it appears the developer was just sending the data with incorrect timezone info.

    I discovered this because when looking at the data, I was expecting peaks at certain busy times, but instead was seeing them when business should've been closed.

    Thanks again.

Page 2 of 2 FirstFirst 1 2

Posting Permissions

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