Hi,
I am trying to enter a specific date in a certain cell based on the date in upper cell and also making it a monthly thing and thus making it a recurring process. now the problem is when the date is becoming older than a month the formula is showing error and I am finding no way to get the generated date to freeze when I first get a valid output date.
all suggestions are welcome.
Hi vishofansh,
I am not sure what you want to do.
Are you trying to increase the date by one month in each row?
Numbers is very good at recognising dates and creating a series.
Here is one idea. insert the first two dates of the series you want to create. Select both cells and hover the cursor over the bottom of the selection to see a yellow dot (the Fill Handle)
Drag the yellow dot down:
Now we have a series of the 30th day of each month.
If that is not what you want, please reply with more information on your overall aim.
Regards,
Ian.
I *think* I understand what you're aiming for...
You have two cells... one with a date value, and you want the second cell to calculate the next month based on that start date.
This is a two-step solution - first you need to work out the difference between the fixed date and the current date. That can be done using DATEDIF(). Assuming your fixed date is in cell B2, the formula:
=DATEDIF(B2,TODAY(),"M")
will give you the number of months between B2 and the current date. This value can be used with EDATE() to calculate a date offset from your start date:
=EDATE(B2,DATEDIF(B2,TODAY(),"M"))
EDATE() adds a number of date units (in this case months, denoted by the "M") to the start date in B2.
thanks Ian,
but I am trying to change months automatically after ending of each cycle by using function. like if I post 1st date as 29/07/2026 then it'll show me 29/08/2026 in the next row but will show 29/09/2026 only after passing of 29/08/2026 and so on.
Recurring monthly date formula errors, won't freeze