If you are searching for a solution or some special tricks to convert time zone to daylight savings in Excel then you have landed in the right place. There are some quick methods to convert time zone to daylight savings in Excel. This article will show you each and every step with proper illustrations so, you can easily apply them for your purpose. Let’s get into the central part of the article.
How to Convert Time Zone with Daylight Savings in Excel: 2 Cases
In this section, I will show you 4 quick and easy methods to convert time zone to daylight savings in Excel on Windows operating system. You will find detailed explanations with clear illustrations of each thing in this article. I have used Microsoft 365 version here. But you can use any other versions as of your availability. If anything of this article doesn’t work in your version then leave us a comment.
✅ When Date and Time Are in One Cell
Often, you have time and date in one cell and often in different cells. I will show you the solution for both cases. Now, I will show you the method to convert the time zone to DLS time when the cell contains both time and date together.
Method 1: Using Formula
In the first method, I will show you the method using a generic formula. For this follow the steps below:
📌 Steps:
- First, insert the DLS hour in cell F5.
- Then, paste the formula given below in cell D5.
=C5-$F$5/24🔎 Formula Explanation
- Here, cell C5 is the cell that contains the time and date value in the normal time zone.
- Cell F5 contains the DLS hours applied for the country.
- $F$5/24 converts the hour to day value and you have to make the cell reference of F5 cell Aboslute so it will befilled for all cells applied.
- Now, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column or use Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste.
- As a result, you will see that, the “Daylight Saving” column is filled with the output of the formula applied. Thus you have got the DLS time of input values.
Method 2: Using TIME Function
Alternatively, you can use the TIME function to do the same task when the date and time are in the same cell together. For this follow the steps below:
📌 Steps:
- For this case, paste the following formula into cell D5.
=C5-TIME($F$5,0,0)Syntax of TIME function: =TIME(hour, minute, second)
So, by this formula, you subtracted the time in cell C5 by 2 hours which is the DLS value inserted in cell F5.
- Now, drag the fill handle icon to paste the similar formula to the other cells of the column.
✅ When Date and Time Are in Different Cells
Now, here I will show the methods to convert the time zone to the Daylight savings system when you have date and time values in different columns.
Method 1: Using Formula to Get DLS Time and Date in Same Cell
In this method, I will use a simple formula without any function to get DLS time and date in the same cell. For this-
📌 Steps:
- Insert the following formula in the cell E5:
=C5+D5- $G$5/24
- Drag the Fill Handle icon till the last cell of the column and you will get the column filled with DLS time for the respective date and time.
Method 2: Using INT and MOD Functions to Get DLS Time and Date in Different Cells
Now, if you want to get the DLS date and time value in different cells then, you have to use the INT and the MOD functions. For this, follow the steps below-
📌 Steps:
- First, create two columns to get DLS date and time outputs.
- Insert the following formula into cell E5 to get the DLS Date value of the input data.
=INT(C5+D5-$H$5/24)
- Then, you have to find the time value of the DLS time zone. For this, insert the following formula into cell F5.
=MOD(C5+D5-$H$5/24,10)
- Now, select the two cells E5 and F5 then drag the Fill Handle icon to the last cell of the table.
- As a result, you will get the columns filled with the DLS date and time.
Things to Remember
- You have to divide the DLS hour by 24 to convert it into numerical time format.
- If you face the format issue then select the cells and change the formats to “Date” or “Time” as per the column value.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
In this article, you have found how to convert time zone to daylight savings in Excel. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.
