"вам нужно знать соответствующее временное смещение между двумя часовыми поясами"
То, что это значение может быть не константным, и зависить от даты/времени указанном в ячейке, совершенно не учитывается.
Kutools for Excel is a powerful add-in that frees you from performing time-consuming operations in Excel, such as combine sheets quickly, merge cells without losing data, paste to only visible cells, count cells by color and so on. 300+ powerful features / functions for Excel 2019, 2016, 2013, 2010, 2007 or Office 365!
It enables tabbed browsing, editing, and managing of Microsoft Office applications. You can open multiple documents / files in a single tabbed window, such as using the browser IE 8/9/10, Firefox, and Google Chrome. It's compatible with Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365. Demo
Kutools for Outlook is a powerful add-in that frees you from time-consuming operations which majority of Outlook users has to perform daily! It can save your time from using Microsoft Outlook 2019, 2016, 2013, 2010 or Office 365!
Kutools for Word is a powerful add-in that frees you from time-consuming operations which majority of Word users have to perform daily! It can save your time from using Microsoft Word / Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365!
Restores the old look and menus of Office 2003 to Microsoft Office 2019, 2016, 2013, 2010, 2007 or Office 365. Don’t lose time in finding commands on the new Ribbon. Easy to deploy to all computers in enterprises and organizations.
Supposing you are working in a foreign company, and you receive lots of reports with different date times from different time zone every day. You need to change those date time from the senders time zones to your current time zone in order to combine all report data together. This article will show you how to convert date time from one time zone to another in Excel.
For example, date time in cell A2:A10 is the Pacific Standard Time (PST), and you may need to convert these time to Beijing time. You can apply the following formula to achieve it.
1. Firstly, you need to know the corresponding time offset between two time zones in order to convert between them. As we know, the time difference between Pacific Standard Time and Beijing time is 16 hours (excluding the daylight saving time).
2. Use the formula =A2+ (16/24) can quickly get the Beijing time based on the Pacific Standard Time. See screenshot:
3. Drag the Fill Handle down to get all Beijing Times you need.
You can easily convert date time from one time zone to another with Add hours to date function of Kutools for Excel. Please do as follows.
Before applying Kutools for Excel, please download and install it firstly.
1. Select a cell (says cell C2) for saving the result, then click Kutools > Formula Helper > Formula Helper. See screenshot:
2. In the Formula Helper dialog box, please configure as follows.
Note: Here we convert date time from Pacific Standard Time to Beijing Time, so the time difference number is 16. In contrary, you need to enter -16 into the Number box.
3. Drag the Fill Handle down to get all Beijing Time based on the PST as below screenshot shown.
Note: You need to change the cell format to date time format after the calculation.