Scanning A Text String Then Month Year Format with Month End Date-CodePudding

I searched on Stackoverflow and was able to write this formula by reading a cell's Month Year Date Time to give me this (Column A from reading Column H):

Column A Cell A2 (A2 indicates 4/1/2021) =DATE(YEAR(H2),MONTH(H2) 1,1) which reads this 3/30/2021 5:09:55 PM.

Then I wrote a formula in Column I for giving me the Month Year format for I2 reading from A2:

=TEXT(A2,"mmmm yyyy") which is April 2021 then I copy and paste as value into Column B (B2 as April 2021)

Is there an IF statement or a formula I can write when scanning say Column I (I2 = "April 2021) to give me the Month~MonthEnd-Date~Year format? ie "April 2021" to "April 30th 2021"?

This might require a new thread but how do I "turn-on" the auto feature whenever I add a new row to keep the new rows as part of a Table?

CodePudding user response：

If you are just looking for the start of the following month in column A, you formula works fine. An alternative formula for column A could be:

=EOMONTH(I2,0) 1

As an alternative to converting the date to a text value, you can format the cell to display date in the same format. The advantage to this is the cell is still an excel date that formulas can easily work with.

Again to get the last day of the month use EOMONTH formula. If you want to keep your text method going, use the following formula:

=TEXT(EOMONTH(A2,0),"mmmm dd yyyy")

Alternatively if you format the cell appropriately, you can just use:

=EOMONTH(A2,0)

Note if you really need the st for days ending in 1 and nd for days end 2, etc, it gets more complicated. I am assuming that they are not really needed.