Recurring date on the same cell based on given date

[Sumeluar]

Good afternoon - I have a task which started on 02/07/23 and is to repeat quarterly always on the seventh day of that month for an indefinite amount of time, how can this be displayed in such a way that the cell always shows when the next quarter is based starting from 02/07/23? The idea is that the same cell would tell me when the next quarterly date is for example: The initial date was February 7, 2023, then the next date will be June 07,2023 and the next one October 7, 2023, and the next February 7, 2024, and so forth.

I've been tinkering with the formula below but unable to modify it to my need.

=DATE(YEAR(DATE(2023,2,7)),MONTH(TODAY()),DAY(7))

Any ideas are greatly appreciated.

Regards!

Sumeluar

 

[Joe4]

The initial date was February 7, 2023, then the next date will be June 07,2023 and the next one October 7, 2023, and the next February 7, 2024, and so forth.

That does not seem to be a quarterly pattern (which would be every 3 months), but rather three times a year (every 4 months).
So which one are you after?

In any event, this can be done pretty easily with the EDATE function, where you take some date and add a set number of months to it.
See: EDATE Function

 

[Sumeluar]

That does not seem to be a quarterly pattern (which would be every 3 months), but rather three times a year (every 4 months).
So which one are you after?

In any event, this can be done pretty easily with the EDATE function, where you take some date and add a set number of months to it.
See: EDATE Function

My bad, every four months.

 

[Joe4]

My bad, every four months.

OK, did you try the EDATE function?, i.e.

=EDATE(original date, 4)

 

[Sumeluar]

That does not seem to be a quarterly pattern (which would be every 3 months), but rather three times a year (every 4 months).
So which one are you after?

In any event, this can be done pretty easily with the EDATE function, where you take some date and add a set number of months to it.
See: EDATE Function

Joe4 - I had been at the at site already but could not make head or tails out of it.

 

[Sumeluar]

OK, did you try the EDATE function?, i.e.

=EDATE(original date, 4)

I tried it but is not providing the desired results. for instance, I changed the starting date from February 7, 2023, to February 7, 2022, and it shows the next quarter form that date in 2022, not the upcoming quarter (June 07, 2023)

 

[Joe4]

I think there is something we are a little unclear about in your question that we need to clarify.

When you say you want the "next date", do you mean that your want the date that is exactly 4 months from your original start date?

Or, do you want the next date in the future (from today), starting from the original start date?
So if your original date was February 7, 2005, the date you would want returned in June 7, 2023?

 

[Joe4]

When you say you want the "next date", do you mean that your want the date that is exactly 4 months from your original start date?

If this first option is what you are after, it is very straightfoward, and you should just be able to use the EDATE function like I showed in previous replies.

Or, do you want the next date in the future (from today), starting from the original start date?
So if your original date was February 7, 2005, the date you would want returned in June 7, 2023?

If this second option is what you are after, then I can come up with a User Defined Function that works.
That code looks like this:

Function NextFutureDate(d As Date, m As Integer) As Date
'   Returns next future date based on starting date and monthly increment
'       d: starting date
'       m: number of months to increment by

    Dim nd As Date
    
    nd = d
    Do While nd < Date
        nd = WorksheetFunction.EDate(nd, m)
    Loop
    
    NextFutureDate = nd
    
End Function

So, for a date in cell G2, you would use this formula on it:

=nextfuturedate(G2,4)

Here are some examples of what that function returns:

 

[Sumeluar]

If this first option is what you are after, it is very straightfoward, and you should just be able to use the EDATE function like I showed in previous replies.


If this second option is what you are after, then I can come up with a User Defined Function that works.
That code looks like this:

Function NextFutureDate(d As Date, m As Integer) As Date
'   Returns next future date based on starting date and monthly increment
'       d: starting date
'       m: number of months to increment by

    Dim nd As Date
   
    nd = d
    Do While nd < Date
        nd = WorksheetFunction.EDate(nd, m)
    Loop
   
    NextFutureDate = nd
   
End Function

So, for a date in cell G2, you would use this formula on it:

=nextfuturedate(G2,4)

Here are some examples of what that function returns:
View attachment 90642

Joe4 - That function works well; I changed it to this:

=NextFutureDate(DATE(2005,2,7),4)

I imagine it is complicated to do a formula instead.


Thank you for the solution.

 

[Joe4]

Joe4 - That function works well; I changed it to this:

=NextFutureDate(DATE(2005,2,7),4)

I imagine it is complicated to do a formula instead.


Thank you for the solution.

You are welcome.
Yes, you can put anything in the first argument that returns a date, whether it is a hard-coded date or a reference to a cell containing a date.
That is the flexibility of using functions.