Forum Discussion
Beginning of Month formula on ID
Tony2021 Perhaps I got it right this time. I give up. Two dates don't seem to agree with yours, though.
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.