SOLVED

Beginning of Month formula on ID

%3CLINGO-SUB%20id%3D%22lingo-sub-3283472%22%20slang%3D%22en-US%22%3EBeginning%20of%20Month%20formula%20on%20ID%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3283472%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Experts%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20a%20formula%20that%20will%20return%20the%20beginning%20of%20the%20month%20for%20the%20first%20record%20of%20each%20LCID%20and%20all%20records%20after%20that%20first%20cell%20then%20return%20the%20beginning%20of%20the%20month%20for%20the%20next%20month.%26nbsp%3B%20For%20the%20next%20ID%2C%20repeat%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20attached%20example.%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20very%20much%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Tony2021_0-1649784419192.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Tony2021_0-1649784419192.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F363490i4D68B2DDAD24F12B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Tony2021_0-1649784419192.png%22%20alt%3D%22Tony2021_0-1649784419192.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3283472%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-3284596%22%20slang%3D%22en-US%22%3ERe%3A%20Beginning%20of%20Month%20formula%20on%20ID%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3284596%22%20slang%3D%22en-US%22%3EI%20now%20see%20I%20had%20a%20typo.%20You%20got%20it%20right.%20You%20could%20read%20my%20mind.%20Amazing.%20thank%20you%20and%20sorry%20for%20the%20confusion.%20It%20is%20perfect!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3284376%22%20slang%3D%22en-US%22%3ERe%3A%20Beginning%20of%20Month%20formula%20on%20ID%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3284376%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3BPerhaps%20I%20got%20it%20right%20this%20time.%20I%20give%20up.%20Two%20dates%20don't%20seem%20to%20agree%20with%20yours%2C%20though.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3284373%22%20slang%3D%22en-US%22%3ERe%3A%20Beginning%20of%20Month%20formula%20on%20ID%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3284373%22%20slang%3D%22en-US%22%3EHi%20Riny%2C%20sorry%20for%20the%20confusion.%3CBR%20%2F%3Emaybe%20we%20are%20mixing%20the%20European%20and%20English%20formats%3F%20I%20hope%20that%20is%20not%20part%20of%20the%20problem.%3CBR%20%2F%3ED1%20%3D%20Oct%201%20and%20not%20sure%20if%20you%20mean%201%2F10%2F2021%20%3D%20Jan%2010%20(but%20I%20assume%20its%20oct%201%20but%20in%20d%2Fm%2Fy.%20I%20assume%20you%20are%20in%20Europe.%3CBR%20%2F%3E%3CBR%20%2F%3ETo%20answer%20your%20questions%3A%3CBR%20%2F%3EFrist%20cell%20%3D%20the%20first%20cell%20for%20that%20ID.%20(ie%20B2%2C%20B6%2C%20B9)%3CBR%20%2F%3ENext%20cell%20%3D%20the%20next%20cell%20for%20that%20ID.%20(ie%20B3%2C%20B7%2C%20B10)%3CBR%20%2F%3E%3CBR%20%2F%3ED1%20(I%20thk%20its%20D2%20actually)%20is%20oct%201%20since%20the%20value%20is%20oct%2015%20in%20B2%20(need%20to%20return%20the%20beg%20of%20the%20month)%3CBR%20%2F%3ED2%20(I%20thk%20its%20D3%20actually)%20is%20Nov%201%20since%20that%20is%20the%20next%20month%20after%20Oct%201.%20(need%20to%20return%20the%20beg%20of%20the%20month)%3CBR%20%2F%3ED5%20is%20jan%201%202022%20since%20D4%20is%20Dec%201%202021%20and%20the%20next%20month%20is%20jan%201%2C%202022.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20hope%20its%20more%20clear.%3CBR%20%2F%3Elet%20me%20know...%20thank%20you%20and%20sorry%20for%20the%20confusion.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3284328%22%20slang%3D%22en-US%22%3ERe%3A%20Beginning%20of%20Month%20formula%20on%20ID%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3284328%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3BNot%20really.%20You%20speak%20of%20the%20%22first%20cell%22%20and%20the%20%22next%22.%20Which%20are%20these%3F%20Why%20not%20write%20in%20plain%20words%20why%20D1%20should%20be%2001%2F10%2F2021%20and%20why%20should%20the%20D2%20become%2001%2F11%2F2021%20etc.%20And%20most%20importantly%2C%20why%20should%20D5%20be%2001%2F01%2F2022%3F%20And%20D6%20jumps%20back%20to%2001%2F12%2F2021%20again.%20I%20just%20don't%20see%20the%20logic.%20Sorry!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3284316%22%20slang%3D%22en-US%22%3ERe%3A%20Beginning%20of%20Month%20formula%20on%20ID%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3284316%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Riny%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20will%20expand%20a%20bit%20and%20hope%20will%20make%20it%20more%20clear.%20I%20will%20take%20a%20different%20approach.%3CBR%20%2F%3E%3CBR%20%2F%3EBasically%2C%20the%20most%20impt%20cell%20is%20the%20first%20cell%20and%20that%20date%20drives%20the%20values%20for%20the%20following%20dates%20for%20that%20ID.%20Essentially%2C%20it%20doesnt%20matter%20what%20is%20in%20the%20next%20cell%20for%20that%20ID.%20I%20could%20simply%20manually%20drag%20the%20date%20up%20until%20the%20next%20ID%20and%20that%20would%20suffice%20but%20I%20am%20looking%20for%20a%20formula.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20tricky%20part%20(to%20me)%20might%20be%20that%20whatever%20the%20value%20is%20in%20the%20first%20date%20for%20that%20ID%2C%20it%20must%20be%20the%201st%20of%20the%20month%20and%20the%20next%20value%201st%20of%20the%20month...etc.%20The%20auto%20fill%20(rt%20click%20and%20drag%20%26amp%3B%20select%20month)%20would%20be%20close%20to%20what%20I%20want%20but%20it%20would%20have%20to%20%22reset%22%20to%20the%201st%20of%20the%20month%20for%20the%20initial%20date%20in%20the%20next%20ID.%3CBR%20%2F%3E%3CBR%20%2F%3Efor%20example%2C%3CBR%20%2F%3Eif%20the%20first%20value%20of%20the%20ID%20is%20Jan%20then%20that%20cell%20should%20be%20Jan%201%20and%20I%20want%20the%20next%20cell%20to%20be%20Feb%201%2C%20Mar%201%2C%20Apr%201...up%20to%20the%20next%20ID%20and%20if%20that%20value%20of%20the%20first%20cell%20for%20that%20ID%20is%20for%20example%20June%20then%20that%20value%20should%20be%20June%201%2C%20then%20July%201%2C%20Aug%201%2C%20Sept%201%20up%20until%20the%20next%20ID%20and%20repeat.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20hope%20that%20clears%20it%20up%20a%20bit.%20Let%20me%20know%20if%20you%20have%20questions.%20thank%20you%20for%20the%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3283970%22%20slang%3D%22en-US%22%3ERe%3A%20Beginning%20of%20Month%20formula%20on%20ID%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3283970%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3BWell%2C%20the%20basic%20formula%20would%20be%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DEOMONTH(B2%2C-1)%2B1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20produces%20the%20same%20results%20as%20my%20first%20formula%2C%20but%20it%20doesn't%20return%20the%20results%20you%20want.%26nbsp%3BI%20don't%20understand%20your%20logic%20for%20some%20of%20the%20dates.%20For%20instance%20B4.%20It%20contains%20the%20date%20of%2030th%20of%20November%20and%20you%20want%20the%20formula%20to%20return%20the%201st%20of%20December.%20The%20next%20entry%20contains%20the%201st%20of%20December%202021%20and%20%22its%20beginning%20of%20the%20month%22%20should%20become%20the%201st%20of%20January%202022.%20And%20there%20are%20others%20like%20that.%20Why%3F%3C%2FP%3E%3CP%3EPerhaps%20you%20made%20some%20typo's%20or%20mixed%20US%20and%20European%20date%20formatting.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3283649%22%20slang%3D%22en-US%22%3ERe%3A%20Beginning%20of%20Month%20formula%20on%20ID%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3283649%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Riny%2C%20please%20see%20attached%20v2.%20Let%20me%20know%20if%20you%20have%20any%20questions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3283615%22%20slang%3D%22en-US%22%3ERe%3A%20Beginning%20of%20Month%20formula%20on%20ID%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3283615%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3BBetter%20to%20upload%20a%20file%20that%20resembles%20your%20real%20situation%20and%20indicate%20how%20the%20end%20result%20should%20look%20like.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3283610%22%20slang%3D%22en-US%22%3ERe%3A%20Beginning%20of%20Month%20formula%20on%20ID%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3283610%22%20slang%3D%22en-US%22%3EHi%20Riny%2C%20ingenious.%20I%20have%20a%20follow%20up%20though.%3CBR%20%2F%3EIf%20the%20following%20date%20value%20is%20in%20the%20same%20month%20then%20I%20would%20need%20to%20use%20the%20next%20month%20(This%20example%20is%20not%20in%20the%20sample%20file%20I%20posted...only%20in%20my%20production%20file).%3CBR%20%2F%3EExample%3A%20date1%20%3D%201%2F1%2F22%20and%20date%202%20%3D%201%2F31%2F22.%3CBR%20%2F%3EI%20would%20need%20the%20output%20to%20be%201%2F1%2F22%20and%202%2F1%2F22%3CBR%20%2F%3EEssentially%20the%20months%20should%20be%20successive%20and%20on%20the%201st%20of%20every%20month%3CBR%20%2F%3E%3CBR%20%2F%3EI%20actually%20do%20not%20need%20the%20test%20for%20blanks%20since%20in%20my%20production%20file%20there%20will%20not%20be%20a%20case%20of%20any%20blanks.%3CBR%20%2F%3E%3CBR%20%2F%3Ethank%20you%20very%20much!%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3283548%22%20slang%3D%22en-US%22%3ERe%3A%20Beginning%20of%20Month%20formula%20on%20ID%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3283548%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3BSee%20attached.%20Formulae%20in%20the%20green%20shaded%20area.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Super Contributor

Hello Experts,

 

I am looking for a formula that will return the beginning of the month for the first record of each LCID and all records after that first cell then return the beginning of the month for the next month.  For the next ID, repeat  

Please see attached example. 

thank you very much

Tony2021_0-1649784419192.png

 

10 Replies

@Tony2021 See attached. Formulae in the green shaded area.

 

Hi Riny, ingenious. I have a follow up though.
If the following date value is in the same month then I would need to use the next month (This example is not in the sample file I posted...only in my production file).
Example: date1 = 1/1/22 and date 2 = 1/31/22.
I would need the output to be 1/1/22 and 2/1/22
Essentially the months should be successive and on the 1st of every month

I actually do not need the test for blanks since in my production file there will not be a case of any blanks.

thank you very much!

@Tony2021 Better to upload a file that resembles your real situation and indicate how the end result should look like.

@Riny_van_Eekelen 

Hi Riny, please see attached v2. Let me know if you have any questions.

 

thank you

@Tony2021 Well, the basic formula would be:

 

=EOMONTH(B2,-1)+1

 

It produces the same results as my first formula, but it doesn't return the results you want. I don't understand your logic for some of the dates. For instance B4. It contains the date of 30th of November and you want the formula to return the 1st of December. The next entry contains the 1st of December 2021 and "its beginning of the month" should become the 1st of January 2022. And there are others like that. Why?

Perhaps you made some typo's or mixed US and European date formatting.

Hi Riny,

I will expand a bit and hope will make it more clear. I will take a different approach.

Basically, the most impt cell is the first cell and that date drives the values for the following dates for that ID. Essentially, it doesnt matter what is in the next cell for that ID. I could simply manually drag the date up until the next ID and that would suffice but I am looking for a formula.

The tricky part (to me) might be that whatever the value is in the first date for that ID, it must be the 1st of the month and the next value 1st of the month...etc. The auto fill (rt click and drag & select month) would be close to what I want but it would have to "reset" to the 1st of the month for the initial date in the next ID.

for example,
if the first value of the ID is Jan then that cell should be Jan 1 and I want the next cell to be Feb 1, Mar 1, Apr 1...up to the next ID and if that value of the first cell for that ID is for example June then that value should be June 1, then July 1, Aug 1, Sept 1 up until the next ID and repeat.

I hope that clears it up a bit. Let me know if you have questions. thank you for the help.

@Tony2021 Not really. You speak of the "first cell" and the "next". Which are these? Why not write in plain words why D1 should be 01/10/2021 and why should the D2 become 01/11/2021 etc. And most importantly, why should D5 be 01/01/2022? And D6 jumps back to 01/12/2021 again. I just don't see the logic. Sorry!

Hi Riny, sorry for the confusion.
maybe we are mixing the European and English formats? I hope that is not part of the problem.
D1 = Oct 1 and not sure if you mean 1/10/2021 = Jan 10 (but I assume its oct 1 but in d/m/y. I assume you are in Europe.

To answer your questions:
Frist cell = the first cell for that ID. (ie B2, B6, B9)
Next cell = the next cell for that ID. (ie B3, B7, B10)

D1 (I thk its D2 actually) is oct 1 since the value is oct 15 in B2 (need to return the beg of the month)
D2 (I thk its D3 actually) is Nov 1 since that is the next month after Oct 1. (need to return the beg of the month)
D5 is jan 1 2022 since D4 is Dec 1 2021 and the next month is jan 1, 2022.

I hope its more clear.
let me know... thank you and sorry for the confusion.
best response confirmed by Tony2021 (Super Contributor)
Solution

@Tony2021 Perhaps I got it right this time. I give up. Two dates don't seem to agree with yours, though.

 

I now see I had a typo. You got it right. You could read my mind. Amazing. thank you and sorry for the confusion. It is perfect!