Nicola has attended:
Excel For Scientists course
Automatic date change
Hello,
I've created a product ordering spreadsheet.
I have a table that says: next order date.
I would like that it automatically updates the date to every 2 weeks from the previous order date. I've tried looking it up but it keeps telling me to use the today formula and I don't want to date to change every time you open the spreadsheet, but rather that once 2 weeks has passed it says the next ordering date will be in the following 2 weeks.
How do I go about doing this?
Many thanks,
Nicola
RE: Automatic date change
Hi Nicola
Thank you for using the forum to ask a question.
You can use the EDATE function in Excel to add a specific number of months to a date. Since you want to add 2 weeks, you can add half a month. Here’s how you can do it:
Assuming your previous order date is in cell A1, you can use the following formula in the cell where you want to display the next order date:
=EDATE(A1, 0.5)
This formula will add half a month (approximately 2 weeks) to the date in cell A1. The date will not change every time you open the spreadsheet, it will remain static until you change the date in cell A1.
Please note that the EDATE function returns the same day of the month in the future month as the day in the start date. So, if the start date is the 31st of a month and the future month does not have 31 days, then it will return the last day of the future month. If this is not what you want, you might need to adjust the formula accordingly.
Also, please remember to format the cell with the formula as a date to see the result as a date. You can do this by selecting the cell, then go to the Home tab, click on the Number Format drop-down menu and select Date.
Kind regards
Richard
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
