First and Last Day of the months formula

Occasional Contributor

Hi everyone I need help with finding a formula that will take the 3 bottom months in chart one and have the formula put the first date of the month and the last day and put that into the correct periods in chart 3(chart 1 order goes from top to bottom current, Period 3,2,1). The formula needs to be able to change the dates if the months changes in chart 1. I hope this makes since and if it doesn't let me know what I can explain better thanks.

6 Replies


Period 3 = April makes no sense to me.

See the attached version. If you change the month numbers in Chart 1, the results in Chart 3 will change automatically.

the only reason period 3 is April because there is no data for march and i only want the the months with data


How can we know which month corresponds to which number if it isn't simply the month number (January=1, etc.)?

That is how I have them I Just skipped march because I only wanted the months with a QTY in chart 1
best response confirmed by allyreckerman (Microsoft)

@TMan5757 Changed the formulae in columns A and B. Added what I think you want in column J, though I'm not sure that your Excel version supports it. Perhaps you need to enter the formula in A3 and confirm it with Ctrl-Shift-Enter and copy it down.


Screenshot 2021-07-21 at 17.27.01.png

File attached.


Thanks for the help it all seems to work