Case 1 – Date and Time Are in One Cell
Convert the time zone to DLS time when the cell contains both time and date.
Method 1 – Using a Formula
Steps:
- Enter the DLS hour in F5.
- Use the formula below in D5.
=C5-$F$5/24Formula Breakdown
- C5 contains the time and date value in the normal time zone.
- F5 contains the DLS hours applied to the country.
- $F$5/24 converts the hours to day value. Set the cell reference of F5 Absolute.
- Drag down the Fill Handle to see the result in the rest of the cells.
- The “Daylight Saving” is displayed.
Method 2 – Using the TIME Function
Use the TIME function.
Steps:
- Enter the following formula in D5.
=C5-TIME($F$5,0,0)Syntax of the TIME function: =TIME(hour, minute, second)
The formula subtracts 2 hours from the time in C5 (the DLS value in F5).
- Drag down the Fill Handle to see the result in the rest of the cells.
Case 2 -Date and Time Are in Different Cells
Convert the time zone to the Daylight savings system:
Method 1 – Using a Formula to Get DLS Time and Date in the Same Cell
Steps:
- Enter the following formula in E5:
=C5+D5- $G$5/24
The formula combines the date and time values and subtracts the DLS hours from it.
- Drag down the Fill Handle to see the result in the rest of the cells.
Method 2 – Using the INT and MOD Functions to Get the DLS Time and Date in Different Cells
Use the INT and the MOD functions.
Steps:
- Create two columns to get the DLS date and time outputs.
- Enter the following formula in E5 to get the DLS Date.
=INT(C5+D5-$H$5/24)
The formula combines the date and time value and subtracts the DLS hours from it; the INT function rounds the values and returns the date value of the DLS time zone.
- To find the time value of the DLS time zone, use the following formula in F5.
=MOD(C5+D5-$H$5/24,10)
The formula combines the input date and time value and subtracts the DLS hours; the MOD function gets the Time value: the remainder of the previous output.
- Select E5 and F5 and drag the Fill Handle icon to the last cell of the table.
- You will see 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 format issues, select the cells and change the format to “Date” or “Time”.
Download Practice Workbook
Download the practice workbook.
Related Articles
<< Go Back to Time Zone in Excel | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
