function to determine next date months

[KayCee]

Hi - I'm trying to see if there's a function that can produce multiple dates in one cell. For reference, I want to create a spreadsheet for meetings based on frequency. If I have an initial meeting date, is there a function that will give me subsequent quarterly dates for future meetings?

 

[Joe4]

Welcome to the Board!

You can use the EDATE function to get the date exactly three months from now.
So, if you had a start date in cell A1, you could get the next quarter date exactly three months from that by putting this formula in cell A2:

=EDATE(A1,3)

Then, if you keep copying that down, it will give the next quarter, then the next, etc.

Whoops, sorry.
Overlooked the "one cell" part. That is what I get for trying to do too many things at once!
Looks like James and Fluff got you squared away.

 

[KayCee]

Glad we could help & thanks for the feedback.

I was working on this spreadsheet and realized I need to formula above to be an IF function for another cell. Will the formula above work with IF? For instance, IF cell I2=Semi-Annually then TEXTJOIN(", ",,TEXT(EDATE(E16,SEQUENCE(2,,6,6)),"mmmm"))

 

[KayCee]

I was working on this spreadsheet and realized I need to formula above to be an IF function for another cell. Will the formula above work with IF? For instance, IF cell I2=Semi-Annually then TEXTJOIN(", ",,TEXT(EDATE(E16,SEQUENCE(2,,6,6)),"mmmm"))

I also have three options.... Annually, Semi-Annually, and Tri-Annually

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C
1	Date
2	27/01/2022	Annually	January
3	27/01/2022	Semi-Annually	July, January
4	27/01/2022	Tri-Annually	April, July, October, January
Main

Cell Formulas
Range	Formula
C2:C4	C2	=LET(s,SWITCH(B2,"Annually",12,"Semi-Annually",6,3),TEXTJOIN(", ",,TEXT(EDATE(A2,SEQUENCE(12/s,,s,s)),"mmmm")))

 

[KayCee]

How about

+Fluff 1.xlsm
	A	B	C
1	Date
2	27/01/2022	Annually	January
3	27/01/2022	Semi-Annually	July, January
4	27/01/2022	Tri-Annually	April, July, October, January
Main

Cell Formulas
Range	Formula
C2:C4	C2	=LET(s,SWITCH(B2,"Annually",12,"Semi-Annually",6,3),TEXTJOIN(", ",,TEXT(EDATE(A2,SEQUENCE(12/s,,s,s)),"mmmm")))

This works great! Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[KayCee]

You're welcome & thanks for the feedback.

I'm having an issue that I didn't realize yesterday where the Tri-Annual isn't working properly. At first it wasn't pulling for tri-annual meeting dates and I realized it's because I left that out of the formula. When I added "Tri-Annual",3,4,4 it's now always pulling 3 dates even when column B doesn't have anything selected in it. If I change column B to either Annual or Semi-Annual, it will change accordingly but I don't want column C to display anything if there's nothing in column B. Do you know of a reason why column C would pull if there's no selection in column C? Below is my formula I'm using and a photo of what I'm getting when I use it. (I tried using the XL2BB feature and it's not working on my computer, unfortunately)

=LET(s,SWITCH(B3,"Annual",12,"Semi-Annual",6,3,"Tri-Annual",3,4,4),TEXTJOIN(", ",,TEXT(EDATE(A3,SEQUENCE(12/s,,s,s)),"mmmm 'y")))

When there is no selection in the meeting frequency for B5 I'm still getting a result in C5 and I don't want that. If I take out Tri-Annual in the formula, Annual and Semi-Annual work fine but it doesn't work when Tri-Annual is in the formula. Am I missing a ( or , in my formula somewhere?

 

[Fluff]

Try it like

=LET(s,SWITCH(B2,"Annual",12,"Semi-Annual",6,"Tri-Annual",4),IF(B2="","",TEXTJOIN(", ",,TEXT(EDATE(A2,SEQUENCE(12/s,,s,s)),"mmmm 'y"))))

 

[KayCee]

Try it like

=LET(s,SWITCH(B2,"Annual",12,"Semi-Annual",6,"Tri-Annual",4),IF(B2="","",TEXTJOIN(", ",,TEXT(EDATE(A2,SEQUENCE(12/s,,s,s)),"mmmm 'y"))))

This formula works perfectly for the cell I'm using it in, thank you! However, this seems to be the ever-evolving spreadsheet on me where I now need to somehow take the information that I'm getting in Next Meeting(s) column and transfer it, one month at a time, into another cell. I want to insert another column at the beginning so that it's column A and be able to sort by a single month based on the meeting frequency and next meeting output. So basically, since Joe Schmo gets 3 meetings a year, he'll show up on this spreadsheet three times. Does this make sense and is it possible?

 

[Fluff]

Can you show what your expected outcome is.