I am trying to come up with a formula to use in one of our Onboarding forms that will automatically calculate the effective coverage date for health and dental insurance based on the day the form is completed. This is what I have =EOMONTH(Date+30,0)+1. Insurance coverage starts the first day of the month after a full 30 days of employment. So if you started on the 1st on the month your insurance coverage would take effect the very next month, but if you started on the 2nd of the month you would have to wait almost two months for it to take effect. I hope that makes sense. I am having trouble with the 1st of the month giving me the date for two months from that date. I am not good at formulas at all. Any help would be much appreciated.
Question
Question
Answer
I think it's just an arithmetic error.
Based on your statement "I am having trouble with the 1st of the month giving me the date for two months from that date", I'm guessing what you are seeing is this:
3/1/2017 returns 4/1/2017 (which is what you expected)
4/1/2017 returns 6/1/2017 (but you are expecting 5/1/2017)
5/1/2017 returns 6/1/2017 (which is what you expected)
6/1/2017 returns 8/1/2017 (but you are expecting 7/1/2017)
Am I Right?
In your formula, you are adding 30 days to the date, then getting the last day of the month, then adding one more day.
So in the case of 4/1/2017 for example, we add 30 days, which gives us 5/1/2017, then we get the end of the month which is 5/31/2017, then we add one day to get 6/1/2017.
I think you need to add 29 days instead of 30. By adding 30, we're really counting 31 days (today plus 30 more days = 31 days total), but you want a total of thirty days (today plus 29 more days = 30 days total). =EOMONTH(Date+29,0)+1
With this change,
3/1/2017 returns 4/1/2017
4/1/2017 returns 5/1/2017
5/1/2017 returns 6/1/2017
6/1/2017 returns 7/1/2017
The only month where the 1st won't return the first of the next month would be February 1st, which would return April 1st.
At the end of the month, is the other time when it gets a little confusing. For example, with the 29 days formula 1/30/2017 will return 3/1/2017. If you think in terms of +30 days, you would be thinking it should be 4/1/2017 and not 3/1/2017. But if you look at it as 1/30/2017 and 1/31/2017 were days 1 and 2, and then 2/1/2017 through 2/28/2017 were days 3 through 30, they reached 30 days by the end of February, so the coverage should start March 1st.
Hope this helps!