One problem for any organization that spans timezones is reporting on log information. For example, my organization collects web proxy logs across the US and Europe. All of these logs are pulled into a central repository (Splunk – one of the greatest worst programs I have ever used). To avoid confusion, all proxy devices report times in UTC.

This is very helpful until HR requests a report for a specific user. They are usually not keen on adjusting the times in their heads. If the date and time were in the same cell, you could just use the formula =A2 – TIME(HOURSBEHINDGMT,0,0). Even this has limitations, though. Here in AZ the formula would always be =A2 – TIME(7,0,0), but for users in other states, you would have to adjust it manually for daylight savings time(DST).

With our system, date and time are reported in separate columns. This means we need to calculate the time adjustment and then check to see if the time change would result in a date change as well. To simplify this process, as well as to deal with the timezone issue above, I put together the following formulas that are pasted into the report before sending to the requestor.

A | B | C | D |

Date | Time | Local Date | Local Time |

11/4/2011 | 14:19:58 | 11/4/2011 | 7:19:58 AM |

This is a sample of the first columns of the report in Excel. When the data is pulled from Splunk, there is only columns A and B. C and D are added and then the following formulas are pasted into each:

C2 (Local Date): =IF(OR($L$2=”AZ”,$L$2=”HI”),IF(HOUR(B2)>IF($L$2=”AZ”,7,10),A2,DATE(YEAR(A2),

MONTH(A2),DAY(A2)-1)),IF(AND(A2>=DATE(YEAR(A2),3,1+((2-(1>=WEEKDAY(DATE(YEAR(A2),3,1))))*7)+(1-WEEKDAY(DATE(YEAR(A2),3,1)))),A2<=DATE(YEAR(A2),11,

CHOOSE(WEEKDAY(DATE(YEAR(A2),11,1)),2,1,7,6,5,4,3))-1),

IF(HOUR(B2)>-$L$2+1,A2,DATE(YEAR(A2),MONTH(A2),DAY(A2)-1)),

IF(HOUR(B2)>-$L$2,A2,DATE(YEAR(A2),MONTH(A2),DAY(A2)-1))))

D2 (Local Time):

=IF(OR($L$2=”AZ”,$L$2=”HI”),IF(HOUR(B2)>IF($L$2=”AZ”,7,10),B2-TIME(IF($L$2=”AZ”,7,10),0,0),B2+TIME(24-IF($L$2=”AZ”,7,10),0,0)),IF(AND(A2>=DATE(YEAR(A2),3,1+((2-(1>=WEEKDAY(DATE(YEAR(A2),3,1))))*7)+(1-WEEKDAY(DATE(YEAR(A2),3,1)))),A2<=DATE(YEAR(A2),11,

CHOOSE(WEEKDAY(DATE(YEAR(A2),11,1)),2,1,7,6,5,4,3))-1),

IF(HOUR(B2)>-$L$2-1,B2-TIME(-$L$2-1,0,0),B2+TIME(24+$L$2-1,0,0)),

IF(HOUR(B2)>-$L$2,B2-TIME(-$L$2,0,0),B2+TIME(24+$L$2,0,0))))

These formulas look for a value in cell L2 to determine what timezone shift to use for displaying dates and times. The acceptable values are integers representing the number of hours the timezone is offset from UTC during standard time. For example, if the user is in California, you would enter “-8” in cell L2. The formula accounts for DST and adjusts the time one hour less between the second Sunday in March and the first Sunday in November. You can also enter “AZ” or “HI” for users in Arizona or Hawaii, as these states do not observe DST.

There are definitely limitations to this approach, as it does not account for the different DST rules in other parts of the world, and technically DST does not begin and end until 2 AM but the users we report on are primarily US workers that work during normal business hours so these limitations are not enough to cause concern for us.

If I am overlooking a more elegant solution, or if you find an error in my formula, please leave a note in the comments below!

Thanks