SOLVED

First and Last Day of the months formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2570510%22%20slang%3D%22en-US%22%3EFirst%20and%20Last%20Day%20of%20the%20months%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2570510%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%20I%20need%20help%20with%20finding%20a%20formula%20that%20will%20take%20the%203%20bottom%20months%20in%20chart%20one%20and%20have%20the%20formula%20put%20the%20first%20date%20of%20the%20month%20and%20the%20last%20day%20and%20put%20that%20into%20the%20correct%20periods%20in%20chart%203(chart%201%20order%20goes%20from%20top%20to%20bottom%20current%2C%20Period%203%2C2%2C1).%20The%20formula%20needs%20to%20be%20able%20to%20change%20the%20dates%20if%20the%20months%20changes%20in%20chart%201.%20I%20hope%20this%20makes%20since%20and%20if%20it%20doesn't%20let%20me%20know%20what%20I%20can%20explain%20better%20thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2570510%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2570595%22%20slang%3D%22en-US%22%3ERe%3A%20First%20and%20Last%20Day%20of%20the%20months%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2570595%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059221%22%20target%3D%22_blank%22%3E%40TMan5757%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPeriod%203%20%3D%20April%20makes%20no%20sense%20to%20me.%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%20If%20you%20change%20the%20month%20numbers%20in%20Chart%201%2C%20the%20results%20in%20Chart%203%20will%20change%20automatically.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2570621%22%20slang%3D%22en-US%22%3ERe%3A%20First%20and%20Last%20Day%20of%20the%20months%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2570621%22%20slang%3D%22en-US%22%3Ethe%20only%20reason%20period%203%20is%20April%20because%20there%20is%20no%20data%20for%20march%20and%20i%20only%20want%20the%20the%20months%20with%20data%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2570838%22%20slang%3D%22en-US%22%3ERe%3A%20First%20and%20Last%20Day%20of%20the%20months%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2570838%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059221%22%20target%3D%22_blank%22%3E%40TMan5757%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20can%20we%20know%20which%20month%20corresponds%20to%20which%20number%20if%20it%20isn't%20simply%20the%20month%20number%20(January%3D1%2C%20etc.)%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
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

@TMan5757 

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

@TMan5757 

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)
Solution

@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