Apr 12 2022 10:31 AM
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
Apr 12 2022 11:03 AM
@Tony2021 See attached. Formulae in the green shaded area.
Apr 12 2022 11:50 AM
Apr 12 2022 11:57 AM
@Tony2021 Better to upload a file that resembles your real situation and indicate how the end result should look like.
Apr 12 2022 12:14 PM
Apr 12 2022 09:24 PM
@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.
Apr 13 2022 04:15 AM - edited Apr 13 2022 04:16 AM
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.
Apr 13 2022 04:37 AM
@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!
Apr 13 2022 05:15 AM
Apr 13 2022 05:28 AM
Solution@Tony2021 Perhaps I got it right this time. I give up. Two dates don't seem to agree with yours, though.
Apr 13 2022 07:38 AM
Apr 13 2022 05:28 AM
Solution@Tony2021 Perhaps I got it right this time. I give up. Two dates don't seem to agree with yours, though.