- To post as a guest, your comment is unpublished.· 3 months agoHi, gada, if you want to go back one month, just use -1 in the formula, like this =DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)), A1 is the cell contain original date, -1 is minus one month from the given date, if you want to go back 3 months, use -3.
How to increment date by 1 month, 1 year or 7 days in Excel?
The Autofill handle is convenient while filling dates in ascending or descending order in Excel. But in default, the dates are increased by one day, how can you increment date by 1month, 1 year or 7 days as below screenshot shown?
With the Fill Series utility, you can increment date by 1 month, 1 year or a week.
1. Select a blank cell and type the starting date.
2. Select a range including starting date, and click Home > Fill > Series. See screenshot:
3. In the Series dialog, do the following options.
1)Sepcify the filling range by rows or columns
2)Check Date in Type section
3)Choose the filling unit
4)Specify the increment value
4. Click OK. And then the selection have been filled date by month, years or days.
If you want to add months, years or days to a date or dates, you can apply a simple formula.
Select a blank cell next to the date you use, type this formula =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) and then press Enter key, drag fill handle over the cells you need to use this formula. See screenshot:
In the formula, A1 is the date you use, if you want to add 1 year to the date, just use =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)), if you want to add 7 days to the date, use this formula =DATE(YEAR(A1),MONTH(A1),DAY(A1)+7).
With Kutools for Excel's Date & Time Helper, you can quickly add months, years or weeks or days to date.
|Kutools for Excel, with more than 300 handy functions, makes your jobs more easier.|
After installing Kutools for Excel, please do as below:（Free Download Kutools for Excel Now!)
1. Select a blank cell which will place the result, click Kutools > Formula Helper > Date & Time helper, then select one utility as you need from the list. See screenshot:
2. Then in the Date & Time Helper dialog, check Add option, and select the date you want to add years/months/days into the textbox of Enter a date or select a date formatting cell section, then type the number of years, months, days, even weeks into the Enter numbers or select cells with contain values you want to add section. You can preview the formula and result in Result section. See screenshot:
3. Click OK. And drag the fill handle over the cells you want to use this formula. See screenshot:
- How To Add/Subtract Half Year/Month/Hour To Date Or Time In Excel?
- Calculate The Difference Between Two Dates In Days, Weeks, Months And Years In Excel
- How To Add Number Of Years Months And Days To Date In Google Sheets?
- How To Add Or Subtract Specific Years, Months And Days(2years4months13days) To A Date In Excel?
- How To Calculate / Get Day Of The Year In Excel?
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 7 months agoi wana go back one month
- To post as a guest, your comment is unpublished.· 2 years agoThe formula will not work for February if you today() occurs on the 29th or 30th... =DATE(YEAR(B8),MONTH(B8)+1,DAY(B8-2)) subtract 2 in the day
- To post as a guest, your comment is unpublished.· 2 years agoYes, there are some shortcoming by using the formula because of the different number of days in months.