How to add/subtract half year/month/hour to date or time in Excel?
To add year, month or hour to date or time is usual in our Excel daily work. Have you ever tried to add half a year, month, or hour to date or time? Here I introduce the tricks to handle with this job.
|Never need to worry about long long formulas in Excel anymore! Kutools for Excel's Auto Text can add all formulas to a group |
as auto text, and liberate your brain! Click here to know Auto Text Click here to get free trial
Here I introduce the formula to help you add half year/month/hour to date or time cell.
|Add half year||=DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))|
|Add half month ||=DATE(YEAR(A1),MONTH(A1)+INT(0.5),DAY(A1)+MOD(0.5,1)*30)|
|Add half hour||=A1+1/48|
Select a cell, and paste one formula as your need from above table, here, I paste =DATE(YEAR(A1),MONTH(A1)+6,DAY(A1)) into a blank cell, B1, and press Enter key to get the result, and if you need you can drag the formula down to fill below cells.
Tip: Within above formulas, A1 is the cell you want to use, you can change it as you need.
To remember above formulas may be somewhat annoy for you. However, with Kutools for Excel’s Formulas function group, you can quickly add or subtract half year, month or hour to date or time without remember formulas.
|Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.|
After free installing Kutools for Excel, please do as below:
1. Select a cell to output the calculate result, and click Kutools > Formulas, and select a formula you need from the list. See screenshot:
2. For instance, select Add months to date form the Formulas group to add half year, and click from Date Time to select a cell you want to use, and enter 6 into the textbox of Number. See screenshot:
Note: You can directly type the date you will add half year to into the DateTime box as “6/11/1990”.
3. Click Ok. And then drag the auto fill handle down to the cells you need.
1. To add half month, select Add days to date from Formulas group, and select the date needed to add, and enter 15 days into the Number textbox. See screenshot:
2. To subtract half year or half month from a date, just change 6 to -6, 15 to -15.
You may be interested in these:
- How to automatically color alternating rows/columns in Excel?
- How to find the max or min value for each day in an Excel range?
- How to add/subtract weeks to date in Excel?
- How to add semicolon to each cell in Excel?
Count times a word(count times of a specific word appears in a single cell or a range . There are dozens of usually used formulas in the Formula Helper.)