formula to get conditional custom dates

Question

Column A contains description in which text after second deli meter contains month numbers >> In A2 02 is February,05 is May ,08 is Aug and last 11 is Nov. if any of these month numbers equals to current year month , in B2 > I need EOMONTH DATE . As cell A2 contains 11 number and current month is November (month number 11) cell B2 EOMONTH date is 30-11-2024. As text in Cell A3 contains 12 ,cell B3 result is EOMONTH of December 2024>>31-12-2024. As on 01-DEC-2024 cell B2 results will be 28-02-2025 and as on 01-JAN-2025 Cell B3 results will be 31-03-2025.

Description	Upcoming date
DD-PRIN2-02-05-08-11	30-11-2024
---	---	---	---
EE-PRIN3-03-06-09-12	31-12-2024

Answer 1

Hi,

In cell B2, enter this formula

=LET(cl,1*(DROP(TEXTSPLIT(A2,"-"),,2)),EOMONTH(DATE(YEAR(TODAY())+AND(NOT(cl>=MONTH(TODAY()))),XLOOKUP(TRUE,cl>=MONTH(TODAY()),cl,TAKE(cl,,1)),1),0))

Hope this helps.

Answer 2

You are welcome.

Answer 3

Sir, Thank you .

Answer 4

if any of the number after 2nd delimeter ("-") of col A value is equal to current month or upcoming month of remaining year or next year B2 and B3 value be the Eomonth of that month number. In my case A2 contains number 11 hence B2 date value = 30-11-2024 and A3 contains number 12 hence B3 date value=31-12-2024.

Incase if A4 text is DD-PRIN3-01-04-07-10 >> as any of the month numbers does not equal to Month(Today()) >> so B4 date value = 31-01-2025 as 01 is the next small number after 2nd deli meter.

In a nut shell find number after 2nd delimeter Col A values return Eomonth of that matches upcoming month of current year or next year in Col B.

Answer 5

Hi there

Please, try the formula

=IF(ISNUMBER(SEARCH(TEXT(MONTH(TODAY()),"00"),TEXTAFTER(A2,"-",2))),EOMONTH(TODAY(),0),"")

I hope this helps you

Regards

Jeovany