PDA

View Full Version : Converting Text to Datetime Format and Then Converting From GMT to Local Time



jbayko
2016-10-13, 09:03 PM
Hello Experts -

I'm receiving text in the following format:

DDD MMM DD YYYY HH:MM:SS TZ
Example: "Thu Oct 06 2016 11:53:28 GMT+0000 (UTC)"

Ultimately, what I want to do is convert this to my local date/time, which is Pacific Daylight Time / Pacific Daylight Saving Time (PDT), so that I can create graphs with my local date/time on the X axis. I imagine that I would need to convert this text to some sort of Datetime object in Excel and then convert it from GMT to PDT.

I've been successful in converting the text string to a date/time object using the following formula:
=DATE(MID(A2,12,4),MONTH(1&MID(A2,5,3)),MID(A2,9,2))+TIME(MID(A2,17,2),MID(A2,20,2),MID(A2,23,2))

However, I have no idea how to give this date/time timezone "knowledge" and/or how to convert from UTC to PDT.

Your help would be greatly appreciated!

Hercules1946
2016-10-13, 09:37 PM
To convert from GMT to PDT, append -(8/24) to your formula

jbayko
2016-10-13, 09:59 PM
To convert from GMT to PDT, append -(8/24) to your formula

Hello -

Thanks for your reply. I'm aware that -x/24 can subtract hours from time. However, this doesn't account for daylight savings time, which causes the hour difference to fluctuate over the course of the year. That's why I was hoping that I could assign a timezone to the Excel date/time object, but it appears from my research that this is not possible.

Are there any simple methods out there for converting time between timezones, which takes daylight savings offsets into account?

Hercules1946
2016-10-13, 10:27 PM
To convert from GMT to PDT, append -(8/24) to your formula

As a further thought, if your base time string is not GMT but (e.g.) GMT+0300 as for Moscow, then the formula would become:
=DATE(MID(A2,12,4),MONTH(1&MID(A2,5,3)),MID(A2,9,2))+TIME(MID(A2,17,2),MID(A2,20,2),MID(A2,23,2))-(8+MID(A2,29,3)+0)/24

Hercules1946
2016-10-13, 10:46 PM
Are there any simple methods out there for converting time between timezones, which takes daylight savings offsets into account?

Thats strange because I thought that GMT wasn't affected by daylight saving. We currently have one hour adjustment in the UK but this is BST British Summer Time 22:40. The GMT time is 21:40

Hercules1946
2016-10-13, 10:56 PM
See if this link helps:

https://www.techwalla.com/articles/how-to-convert-gmt-time-in-microsoft-excel

jbayko
2016-10-14, 12:11 AM
Thats strange because I thought that GMT wasn't affected by daylight saving. We currently have one hour adjustment in the UK but this is BST British Summer Time 22:40. The GMT time is 21:40

Correft. GMT (the timeszone of the source text data) is not affected by daylight savings. However, the target timezone I want to covert to (PDT) is affected by daylight savings, so I must take it into account.

jbayko
2016-10-14, 12:14 AM
See if this link helps:
<snip>

Thanks for the link, but this says nothing more than what was already established in post #2 - that you can add/subtract hours by using x/24.

It doesn't address daylight savings time at all.

Pecoflyer
2016-10-14, 11:48 AM
Lots of information at http://www.cpearson.com/Excel/LocalAndGMTTimes.htm ( see table with VBA functions at the end)

Hercules1946
2016-10-14, 12:42 PM
Thanks for the link, but this says nothing more than what was already established in post #2 - that you can add/subtract hours by using x/24.

It doesn't address daylight savings time at all.

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."

jbayko
2016-10-14, 07:13 PM
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.

jbayko
2016-10-14, 07:14 PM
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.

Hercules1946
2016-10-14, 09:44 PM
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 :)

jbayko
2016-10-14, 10:18 PM
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.

Hercules1946
2016-10-15, 01:45 PM
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! :)

jbayko
2016-10-15, 07:54 PM
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.

Hercules1946
2016-10-15, 10:46 PM
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!

jbayko
2016-10-15, 11:04 PM
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.