A) local time in many cases is not a simple offset throughout the whole year. ( DST, Daylight saving time )
B) the local time definition may change from one year to the next due to political changes.
If you are working in an international company, you may need to know and deal with different local times of multiple zones. And to get the different local times, you need to use UTC/GMT time. Here this tutorial can tell you how to calculate the local time based on the UTC/GMT time in Excel.
UTC—Coordinated Universal Time
GMT -- Greenwich Mean Time
Add Hours/Munites/Seconds to date (add a specific number of hours/minutes/seconds to a given date.)
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
Supposing there is a city—Tokyo whose local time is ahead 9 hours to GMT time, and you can convert the GMT time to its local time by following these steps：
1. Select a blank cell and enter the current GMT time into it, here take A2 for instances. To get the current GMT, you can take reference from this http://www.timeanddate.com/worldclock/
2. Select a blank cell and enter one formula of below, and press Enter to get the local time. See screenshot:
=A2 + TIME(9,0,0)
=A2 + (9 / 24)
(1) In above formulas, 9 is the number of hours the local time ahead to GMT, and you can change it as you need, if the local time is backward to the GMT, you just can change the plus sign + to minus sign -.
(2) The formula =A2 + (9 / 24) will return a decimal number. For converting the decimal number to time, please select the decimal number, and click Home > Number Format > Time.
There is a handy tool – Formulas group in Kutools for Excel, which includes multiple useful and powerful functions can help you do some complex calculation. And to convert GMT/UTC to local time, the Add hours to date of Formulas can do you a favor.
|Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.|
After free installing Kutools for Excel, please do as below:
1. Enter the GMT time into a blank cell, A2 for instance, and select a blank cell, B2, which will get the local time you want. See screenshot:
2. Click Kutools > Formulas > Add hours to date, and in the Formula Helper dialog, select the GMT time to Date Time textbox, and enter the time difference between local time and GMT time in to Number textbox. See screenshot:
3. Click Ok, and the local time has been gotten. See screenshot: