I want to check the value of a date with MONTH(date1), and add a value to another date “date2” based on that value (31 for most months, 30 for April June September and November, 28 for February unless it’s a leap year in which case it will be 29), and have it accumulate as the months go forward. So if date 2 (in dd/mm/yyyy format) is 10/07/2020, then when date 1 reaches August, we add 31 days to date 2 to reach 10/08/2020, and so forth depending on the number of days in a given month. In so doing, I want to be able to count the number of 10th day of the month I’ve accumulated over time. I cannot do this by approximating the average number of days in a month as 30, 30.42 or anything like that (the month counter will then trigger at day values which aren’t 10), so I need both granular conditionality for number of days in a month, and the ability to accumulate the aforementioned 10th day. In other words, when the month changes, I do not want that count of 10 day occurrences to decrease, but to iteratively increase over time. To keep the problem somewhat simple, let’s account for the differences between the 31-day months and the 30-day months (for the latter, an example would be IF(MONTH(date1) = 9 for September…); once that’s done I will account for the two February cases by myself.
Thinking about it, could an easier condition be to just iterate a counter when the 10th day occurs and accumulate that, regardless of how many days a given month has?
EOMONTH returns the last day of a month which falls x number of months away from a date.
Why not simply use EDATE(date;1)? It will give you the date with the month increased, which is adding “31, or 30, or 29, or 28” days.
But IIUC the “immutably”, you might want to use macros that change cell data, not formulas that only compute values.
Have you looked at the DAY() and DAYSINMONTH() functions? It seems they, perhaps in conjunction with the suggestion by @EarnestAl might provide a solution.
If this answer helped you, please accept it by clicking the check mark ? to the left and, karma permitting, upvote it. That will help other people with the same question.
In case you need clarification, edit your question (not an answer) or comment the relevant answer.