Formula to display each month and year of biennium

Paul G.

I am needing help with a formula to display the text month and year or the numeric month and year of each month of a biennium. I am currently developing a "template" metric sheet that will pull data from a report containing various months from the biennium. From this a dashboard will be developed.

The metrics sheet contains a date column where the user will select the first day of the biennium, July 1st.

The column headers are named Month1, Month2, Month3, ... Month24.

For reviewing the metrics sheet there is a row to display the month and year for each column. See image below. I have the formula developed for the first month and year to display. I would like to develop a formula which would be based on either the first month and year displayed or on the BienniumBeginDate so I can drag it across the row to be used for the remaining 23 months. In Excel, depending on your preference of functions it could be written as =TEXT(EDATE(B2,1),"mmmm yyyy")

I have reviewed a number of similar requests, but most are adding several months or involve helper columns. I have tried adapting but have not been able to figure this out.

Any help would be greatly appreciated.

Thank you.

Paul

Hollie Green

use the above formula for the first column then change the formula in the Month 2 column to the below then you should be able to drag it across and get the results you are looking for.

=IF(CONTAINS("January", [Month 1]@row), "February" + " " + RIGHT([Month 1]@row, 4), IF(CONTAINS("February", [Month 1]@row), "March" + " " + RIGHT([Month 1]@row, 4), IF(CONTAINS("April", [Month 1]@row), "May" + " " + RIGHT([Month 1]@row, 4), IF(CONTAINS("May", [Month 1]@row), "June" + " " + RIGHT([Month 1]@row, 4), IF(CONTAINS("June", [Month 1]@row), "July" + " " + RIGHT([Month 1]@row, 4), IF(CONTAINS("July", [Month 1]@row), "August" + " " + RIGHT([Month 1]@row, 4), IF(CONTAINS("August", [Month 1]@row), "September" + " " + RIGHT([Month 1]@row, 4), IF(CONTAINS("September", [Month 1]@row), "October" + " " + RIGHT([Month 1]@row, 4), IF(CONTAINS("October", [Month 1]@row), "November" + " " + RIGHT([Month 1]@row, 4), IF(CONTAINS("November", [Month 1]@row), "December" + " " + RIGHT([Month 1]@row, 4), IF(CONTAINS("December", [Month 1]@row), "January" + " " + (VALUE((RIGHT([Month 12]@row, 4))) + 1), IF(CONTAINS("March", [Month 1]@row), "April" + " " + RIGHT([Month 1]@row, 4), ""))))))))))))

Hollie Green

Sorry forgot to include to make sure your columns are Text/Number and note date or it won't work it will give an invalid data type date expected error.

Hollie Green

Have you tried this?

=IF(MONTH(BienniumBeginDate@row) = 1, "January", IF(MONTH(BienniumBeginDate@row) = 2, "February", IF(MONTH(BienniumBeginDate@row) = 3, "March", IF(MONTH(BienniumBeginDate@row) = 4, "April", IF(MONTH(BienniumBeginDate@row) = 5, "May", IF(MONTH(BienniumBeginDate@row) = 6, "June", IF(MONTH(BienniumBeginDate@row) = 7, "July", IF(MONTH(BienniumBeginDate@row) = 8, "August", IF(MONTH(BienniumBeginDate@row) = 9, "September", IF(MONTH(BienniumBeginDate@row) = 10, "October", IF(MONTH(BienniumBeginDate@row) = 11, "November", IF(MONTH(BienniumBeginDate@row) = 12, "December")))))))))))) + " " + YEAR(BienniumBeginDate@row)

Paul G.

Thank you @Hollie Green for your response.

This formula would display the same results for each month (Month1, Month2, Month3, etc.)

Sorry for any confusion in the way I described what I am trying to accomplish. Let me try it again.

The Biennium Begin Date is 07/01/21. I am using a formula similar to your suggested formula to display July 2021 for Month1. I am looking for a formula for Month2 which will look at July 2021 in Month1 and display August 2021. Then dragging the formula to the right each following month would display the next month. So, Month2 would display August 2021, Month3 would display September 2021, Month4 would display October 2021, etc.

Hopefully that provides clarity to my request.

Thanks again.

Paul

Hollie Green

use the above formula for the first column then change the formula in the Month 2 column to the below then you should be able to drag it across and get the results you are looking for.

=IF(CONTAINS("January", [Month 1]@row), "February" + " " + RIGHT([Month 1]@row, 4), IF(CONTAINS("February", [Month 1]@row), "March" + " " + RIGHT([Month 1]@row, 4), IF(CONTAINS("April", [Month 1]@row), "May" + " " + RIGHT([Month 1]@row, 4), IF(CONTAINS("May", [Month 1]@row), "June" + " " + RIGHT([Month 1]@row, 4), IF(CONTAINS("June", [Month 1]@row), "July" + " " + RIGHT([Month 1]@row, 4), IF(CONTAINS("July", [Month 1]@row), "August" + " " + RIGHT([Month 1]@row, 4), IF(CONTAINS("August", [Month 1]@row), "September" + " " + RIGHT([Month 1]@row, 4), IF(CONTAINS("September", [Month 1]@row), "October" + " " + RIGHT([Month 1]@row, 4), IF(CONTAINS("October", [Month 1]@row), "November" + " " + RIGHT([Month 1]@row, 4), IF(CONTAINS("November", [Month 1]@row), "December" + " " + RIGHT([Month 1]@row, 4), IF(CONTAINS("December", [Month 1]@row), "January" + " " + (VALUE((RIGHT([Month 12]@row, 4))) + 1), IF(CONTAINS("March", [Month 1]@row), "April" + " " + RIGHT([Month 1]@row, 4), ""))))))))))))

Hollie Green

Sorry forgot to include to make sure your columns are Text/Number and note date or it won't work it will give an invalid data type date expected error.

Paul G.

Thank you @Hollie Green.

=IF(CONTAINS("January", [Month1]@row), "February" + " " + RIGHT([Month1]@row, 4), IF(CONTAINS("February", [Month1]@row), "March" + " " + RIGHT([Month1]@row, 4), IF(CONTAINS("April", [Month1]@row), "May" + " " + RIGHT([Month1]@row, 4), IF(CONTAINS("May", [Month1]@row), "June" + " " + RIGHT([Month1]@row, 4), IF(CONTAINS("June", [Month1]@row), "July" + " " + RIGHT([Month1]@row, 4), IF(CONTAINS("July", [Month1]@row), "August" + " " + RIGHT([Month1]@row, 4), IF(CONTAINS("August", [Month1]@row), "September" + " " + RIGHT([Month1]@row, 4), IF(CONTAINS("September", [Month1]@row), "October" + " " + RIGHT([Month1]@row, 4), IF(CONTAINS("October", [Month1]@row), "November" + " " + RIGHT([Month1]@row, 4), IF(CONTAINS("November", [Month1]@row), "December" + " " + RIGHT([Month1]@row, 4), IF(CONTAINS("December", [Month1]@row), "January" + " " + (VALUE((RIGHT([Month1]@row, 4))) + 1), IF(CONTAINS("March", [Month1]@row), "April" + " " + RIGHT([Month1]@row, 4), "")))))))))))) worked and displays each month and the year for the entire biennium.

I appreciate your help.

Paul