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.
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.
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.