How To Add Or Subtract Years, Months, Days, Hours, Minutes, Seconds To A Date And Time In Excel

In this article, I will tell you how to add or subtract years, months, days to an excel Date value, it will also tell you how to add or subtract hours, minutes, seconds to an excel time value with examples.

1. How To Add Or Subtract Years, Months, Days To An Excel Date Value.

1. In Excel, you can use the DATE function to add or subtract years, months, days to a given date value.
2. The syntax for the DATE function is DATE(year, month, day).
3. For example, if you want to add 2 years, months, and hours to a date, you can use the following formula: =DATE(YEAR(date)+2, MONTH(date)+2, DAY(date)+2).
4. You should replace the ‘date’ value with the date value you wish to add 2 years, months, and days to.
5. Below, I will show you some examples of how to do it.

1.1 Add / Subtract Years, Months To Excel Date Value Example.

1. Below is the example data cells. The column A contains a series of date values.
2. You should notice, to show date format string, you should select the cells in column A, then click the Home tab on excel top area, and select the Short Date item in the Number group
   

   	A	B	C
   1	Date Value	Add Half Year	Subtract 2 Months
   2	1/1/2023	7/1/2023	11/1/2022
   3	1/2/2023	7/2/2023	11/2/2022
   4	1/3/2023	7/3/2023	11/3/2022
   5	1/4/2023	7/4/2023	11/4/2022
   6	1/5/2023	7/5/2023	11/5/2022
   7	1/6/2023	7/6/2023	11/6/2022

3. If you want to add half year to the date value in column A and save the result in column B.
4. You should input the formula =DATE(YEAR(A2), MONTH(A2)+6, DAY(A2)) in cell B2.
5. When you press the enter key, it will show the date value 7/1/2023 in cell B2.
6. Then you can autofill (refer to article Flash Fill In Excel & How To Autofill Formula In Excel ) the date value in cell range B3:B7.
7. If you want to subtract 2 months to the date value in column A and save the result in column C.
8. You need to input the formula =DATE(YEAR(A2), MONTH(A2)-2, DAY(A2)) in cell C2.
9. When you press the enter key and auto fill the cell range C3:C7, you will get the date value in column C.

2. How To Add Or Subtract Hours, Minutes, Seconds To An Excel Time Value.

1. In Excel, you can use the TIME function to add or subtract hours, minutes, seconds to a given time value.
2. The syntax for the TIME function is TIME(hour, minute, second).
3. For example, if you want to subtract 2 hours, minutes, and seconds to a time, you can use the following formula: =TIME(HOUR(time)-2, MINUTE(time)-2, SECOND(time)-2).
4. You should replace the ‘time’ value with the value you wish to subtract 2 hours, minutes, and seconds to.
5. Below, I will show you some examples of how to do it.

2.1 Add / Subtract Hours, Minutes, Seconds To Excel Time Value Example.

1. First, input the time values in column A. Select the cells in column A, then click the Home tab, and select the Time item in the Number group to display the time format string in the cell.
   

   	A	B
   1	Time Value	Subtract 2 Hours & Add 6 Minutes
   2	10:50:36 AM	8:56:36 AM
   3	11:50:36 AM	9:56:36 AM
   4	12:50:36 PM	10:56:36 AM
   5	1:50:36 PM	11:56:36 AM
   6	2:50:36 PM	12:56:36 PM
   7	3:50:36 PM	1:56:36 PM

2. Input the formula =TIME(HOUR(A2)-2, MINUTE(A2)+6, SECOND(A2)) in cell B2, and auto fill the cell range B2:B7.
3. Then you can get the result in column B shown above.