Is there a formula to change a number to a month? Or calculate number of days in a month from a #?

AnnieSE

My ultimate goal is to get the Month Start and Month End for any given month so I can then calculate the # of Days in a Month.

The # of Days each month is what I need for my process.

Currently, I have the below setup to work with some other formulas I have going on. The month is displayed as a number that I manually change each month. So "4" is April.

If there is a shortcut to get the number of days in the month of April that would be excellent.

If not, then is there a way, in the cell below "4", I can change it say April? And then, is there a formula I can use to determine the first day of April, then the last day of April, then I can subtract those two?

Any help is appreciated!

Paul Newcome

Yes. Give the numerical version a try. That should clear up the issue.

AnnieSE

Sorry, I figured out how to make it say April. I used just an IF formula.

=IF([Column4]59 = 4, "April")

However, if anyone can answer the rest, that would be fabulous! That is:

- Is there a formula to get the number of days in the month of April for this year?

-If not, is there a formula to determine the first day of April, then the last day of April, then I can subtract those two?

Thank you!

Paul Newcome

I think maybe you are overthinking this part:

"-If not, is there a formula to determine the first day of April, then the last day of April, then I can subtract those two?"

If you can figure out the last day, then you already know how many days are in the month.

The easiest way to do this is to take the first day of the following month and then subtract one day from that. Pulling the day number from that date will give you how many days are in the month. In the below you can use a cell reference to pull in the year and month numbers from your sheet.

=DAY(IFERROR(DATE(cell_reference_for_year, cell_reference_for_month + 1, 1), DATE(cell_reference_for_year + 1, 12, 1)) - 1)

AnnieSE

@Paul Newcome Hello Paul, thank you for your response!

I tried the formula provided, but the output is '30' no matter the month entered. Is there something I can add to account for months with 31 days, or shorter months like February?

Paul Newcome

It should be working. what are you putting in the sections where it says "cell_reference_for..."?

AnnieSE

@Paul Newcome

I'm using the circled cells - should I be using the numerical version (2) for the month instead of text (February)?

Paul Newcome

Yes. Give the numerical version a try. That should clear up the issue.

AnnieSE

@Paul Newcome It worked! I was definitely overthinking 😅

Thank you so much for your help!

Paul Newcome

Happy to help. 👍️