I need to identify the time of day that a survey was taken by a respondent so I can do time series analysis - and thus it must be identified using the local time for the respondent. I know I can convert the UTC time to Eastern time using the Excel format below but don't know how to make the various adjustments for each time zone, and the survey will be in the field when Daylight Savings Time ends Sun Nov 1st so I'm not clear on how to make that adjustment either. We are collecting Zip Code so we can identify the location of each respondent.
=(((A2/60)/60)/24)+DATE(1970,1,1)+(-5/24)
Help?