Welcome toExcel.Tips.Net
Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment
ExcelTips FAQ
ExcelTips Premium
Learn Access Now
Free Printable Forms
Beauty Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Legal Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
Wedding Tips
Word2007 Tips
WordTips
Advertise on the
ExcelTips Site
Filtering Columns for Unique Values
Printing Multiple Worksheets on a Single Page
David collects data from two locations, one on the East coast and the other on the West coast. After collecting the East coast times, he needs to adjust them for the time zone differences. He is wondering how to adjust the East coast times by three hours so they represent West coast times.
How to adjust an East coast time by three hours to be consistent with West coast times depends on the nature of the times you are adjusting. In general, if the data is stored as an Excel date/time serial number, then the adjustment is easy. All you have to do is remember that in the serial number format, anything to the left of the decimal point is days and anything to the right of the decimal point is partial days (hours, minutes, and seconds).
Since an hour is 1/24 of each day, three hours would be 3/24, or 0.125. Simply subtract this value from the serial value stored in the worksheet, and you've adjusted the time for the difference in time zones. The actual formula is easy:
=A1-0.125
You have to be a bit careful in doing this sort of adjustment with your date/time serial numbers, however. If the values in the worksheet are simply time values (there is no date component to the left of the decimal point), then subtracting 0.125 from the time value can result in an erroneous result if the original time is anywhere between midnight and 3:00 am. The way around that is to make the formula just a bit more complex:
=IF(A1<0.125,A1+0.875,A1-0.125)
What this formula does if the value in A1 is strictly a time value, between midnight and 3:00 am, is to add 21 hours (21/24 or 0.875) to the value, providing the expected result of an adjusted time between 9:00 pm and midnight.
Another potential gottcha regards the actual time zones in which the East coast data is collected. If the data is collected from a single known location, it is no big deal—you can look at a map and figure out what time zones are in play. If the data is collected from all over the Eastern time zone, then the problem is determining whether the data should be adjusted by either two or three hours. You see, some areas in the Eastern time zone don't change to Daylight Savings Time uniformly, so it is possible that at some times of the year the adjustment to the time may be two hours and other times of the year it may be three.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3259) applies to Microsoft Excel versions: 97 2000 2002 2003 2007
A Picture is Worth Thousands! Your worksheets are not limited to holding numbers and text. You can also add graphics or easily create charts based on your data. Excel Graphics and Charts, available in two versions, helps you make your graphics and charts their absolute best.