Next n-th day of the month - excel

Question

Let's say I wanna calculate my next paycheck date every day in excel. I get money each 5th day of the month. How can i do it?

Expected outcome:

Answer 1

Or:

=EOMONTH(A2,-(DAY(A2)<=5))+5

---

Edit

As requested a short summary of how I got to the above:

Initially started of with thinking about checking the number of the day of the month in an IF-statement.

=IF(DAY(A2)<=5,A2+5-DAY(A2),EDATE(A2+5-DAY(A2),1))

This would check if the returned value is smaller or equal to 5. If so, then it would add the remainder of 5 minus the returned value to the current date. If false, then it would use EDATE to get the same date of the previous calculation in the next month.

Since this looked still like it had redundant parts I started looking for a different approach where I wouldn't have to do the same calculation twice. Ended up with:

=EOMONTH(A2,IF(DAY(A2)<=5,-1,0))+5

Yet, still the IF is redundant because I need either to get a -1 or a zero as the second argument in EOMONTH. Therefor we just need the expression to return TRUE or FALSE and multiply it by -1 or rather -.

Answer 2

Perhaps like this:

=DATE(YEAR(A2),MONTH(A2)+IF(DAY(A2)<=5,0,1),5)