Written by Allen Wyatt (last updated July 11, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021
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)
If the value in A1 is strictly a time value, between midnight and 3:00 am, the formula adds 21 hours (21/24 or 0.875) to the value, providing the expected result of an adjusted time between 9:00 pm and midnight.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9309) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel here: Adjusting Times for Time Zones.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Want to display an elapsed time as a decimal format? It's pretty easy to do, if you know that a very simple formula is ...
Discover MoreExcel is great at working with times and dates. Sometimes, though, it can be a bit tricky to figure out how to work with ...
Discover MoreWhen you enter a time value into Excel, the program tries its hardest to make the value into a valid time. This can lead ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments