How to Check a datetime is in GMT or BST

[mike08]

Hi mates,

Once more i need ur valuable help. In excel, i have a column
"Received On" contains the datetime of a mail received , it is in BST, sometimes it should be in GMT depends on the date or year, i'm not sure.
So what my query is, in another column, i have to check if the datetime in "Received On' is in BST, convert this datetime to GMT, otherwise if this time is in GMT, i don't need to add anything just copy that datetime alone.

eg: ie in ColumnA is the Datetime value

if ColumnA=BST then
ColumnB=ColumnA+1/24 (ie convert BST to GMT)
else if ColumnA=GMT then
ColumnB=ColumnA (ie not need to change, becz already columnA is in GMT)

are u clear? if u have confusion, pls ask me.

Thanks in advance for any help

 

[Andrew Poulsom]

This may help you:

http://www.cpearson.com/Excel/TimeZoneAndDaylightTime.aspx

 

[mike08]

Thanks Andrew,

that means using scripting only, i can solve my query. But is there any way to check this using excel static functions

 

[Andrew Poulsom]

No, there isn't an Excel function to determine whether a date/time is GMT or BST. But I also found this UDF:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=813

 

[barry houdini]

If you want to convert BST to GMT you need to subtract an hour. Given that BST runs from 01:00 on the last Sunday in March to 01:00 on the last Sunday in October you can use this formula to subtract an hour from a "datetime" in A2 when required or leave alone if it's already BST.

=IF(A2="","",A2-IF(PRODUCT(A2-DATE(YEAR(A2),{4,11},1-WEEKDAY(DATE(YEAR(A2),{4,11},0)))-1/24)<0,1)/24)

Note that for times between 01:00 and 02:00 on last Sunday in October it's impossible to tell whether it's BST or GMT because that hour is covered twice, once in BST then once in GMT.....

This formula is valid for current dates but may not be valid if you go back more than 10 years as the changeover dates were sometimes different.

 

[Richard Schollar]

X 100

 

[Andrew Poulsom]

=IF(A2="","",A2-IF(PRODUCT(A2-DATE(YEAR(A2),{4,11},1-WEEKDAY(DATE(YEAR(A2),{4,11},0)))-1/24)<0,1)/24)

Good Lord!

 

[mike08]

Hi barry,

its great, but i am not familiar with functions you used, i tried a lot my own way, no luck to understand. sorry, so can u explain each functions u used in the formula? pls...
Why u used A2="", not getting , i think are u happy to help me or any one can ........

 

[barry houdini]

The part

=IF(A2="",""....just checks whether A2 is empty and returns a blank if it is......if you always have a datetime in A2 you can just use this:

=A2-(PRODUCT(A2-DATE(YEAR(A2),{4,11},1-WEEKDAY(DATE(YEAR(A2),{4,11},0)))-1/24)<0)/24

The general principle is as follows:

The DATE part generates an array of two dates - both the start and end of BST for the year in question. These dates are subtracted from the datetime in A2 (with an adjustment for the time of day of the BST/GMT switch). If the resultant numbers are both positive then A2 is after the end of BST, if both are negative then A2 is before BST.....if you have one of each then it must be within the BST period.

Using PRODUCT multiplies the two numbers, so a negative result from the Product of the 2 indicates 1 of each, i.e. within BST and so one hour (1/24) is subtracted.

The dates themselves are generated using a variation of this formula

=DATE(YEAR(A2),4,1-WEEKDAY(DATE(YEAR(A2),4,0)))

This finds the last Sunday in March (by finding the first Sunday in April and subtracting 7), so we can extend it to find both the last Sunday in March and the last Sunday in October by replacing 4 with {4,11}

 

[mike08]

Hi thanks a lot man, u r really great, along with the solution, u are giving me a detailed explanation. its very useful for me.
thanks once more