Adding the following month using same format EXCEL

1
votes

I hope you all are healthy during these strange times. I do have a question in regards to creating a date in excel. I am trying to auto populate the date based on the current date. First the date pops up, followed by the month and year under that (i.e. 4/9/2020 will populate as APR-20 underneath that)... Finally from the formula I used to create the specific date and year format (MMM-YY) I need to create another date with the same format but for the following month (i.e. APR-20 is the current month and year, but now also need the formula to show me MAY-20 (format MMM-YY). This is the formula I used in excel to create the current month and year. Thank you guys so much!

=UPPER(TEXT(A1,"MMM")&"-"&TEXT(A1,YY))

A1 = the current date of 4/9/2020

Thank you again!

2
exceldateexcel-formula
Why not just use EOMONTH to add a month to A1. – BigBen
Btw your formula can be simplified to =UPPER(TEXT(A1, "Mmm-yy")). – BigBen
with just a little tweak to your formula =TEXT(MONTH(A1);"MMM")&"-"&YEAR(A1) – emiljoj
I did use EOMONTH in the formula creating the date. we go into 8 days into the next month so the date if it goes outside of the month we are working in, it needs to stay the previous month. Thank you for simplifying the month and year formula! – Christopher J Rode

2 Answers

0
votes

Put this in A2 and copy down:

=UPPER(TEXT(EOMONTH($A$1,ROW(A1)-1),"MMM\-YY"))

enter image description here

0
votes

You Can Perform this by EOMONTH function. It Returns the serial number for the last day of the month that is the indicated number of months before or after start date.

Syntax : EOMONTH(start_date, months)

For more details you can check this link https://support.office.com/en-us/article/eomonth-function-7314ffa1-2bc9-4005-9d66-f49db127d628