If formula to obtain current month and year

[LambertyE]

Hello Excel Fam,

I am trying to get around this formula.

Idea:
If a cell is not empty then the expected result is the current month and year.

Example:
When there is a date (or not empty cell) in column B then results should be current month and year column E. As shown below in red.

I have tried the following formula among others:
=IF(B2<>"","mm/yy")
=IF(C2<>"",MONTH(YEAR(TODAY())))

But the results I'm getting are 1/12 & 1/7, not even close to the correct results. Can someone shed some light?

Thanks in advance!

A	B	C	D	E
Paid	7/31/2020	9/19/2020	Yes	8/20
Paid	8/31/2020	10/20/2020	Yes	9/20
Paid	9/30/2020	11/19/2020	Yes	10/20
Pending	10/30/2020		Yes	11/20

 

[jasonb75]

What you're asking for will be

=IF(B2<>"",TEXT(TODAY(),"mm/yy"),"")

Although I should point out that any formula using TODAY() for the current date will show the current date when you're viewing the sheet, not the current date when the data was entered.
If you want to lock the date in place then you will need to use either vba or circular references.

 

[LambertyE]

What you're asking for will be

=IF(B2<>"",TEXT(TODAY(),"mm/yy"),"")

Although I should point out that any formula using TODAY() for the current date will show the current date when you're viewing the sheet, not the current date when the data was entered.
If you want to lock the date in place then you will need to use either vba or circular references.

Thank you for the quick response!

Does that mean that every time the spreadsheet is viewed the date will change? If yes, how can be overcome? what is needed is the month and year the invoice belongs to. Any ideas as to how we can get around that. Your input is greatly appreciated!

Thanks again!

 

[jasonb75]

Based on what I see in your examples (column E is the month following the date in column B), try

=IF(B2="","",EOMONTH(B2,1))

with a custom format of mm/yy

 

[LambertyE]

Based on what I see in your examples (column E is the month following the date in column B), try

=IF(B2="","",EOMONTH(B2,1))

with a custom format of mm/yy

Thank you!

So sorry to bother, we are getting closer though, the results with this formula are coming with month and day (i.e.: 1/31, 2/29 ) but what I need is month and year (i.e.: 1/20, 2/20, etc).

 

[jasonb75]

That would be the wrong format applied. The formula produces the last day of the month, the format dictates which part is displayed. mm/yy shows month and year (may be different if your system settings are in a language other than english).