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
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 – Formula Helper 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.
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 > Formula Helper > Formula Helper, then in the popping Formulas Helper dialog, select Add hours to date from the Choose a formula list, and in Arguments input section, 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: