I have a sheet with dates in column A and a formula in B that populates a date on the 15th of the following month as follows A1 = 01/01/2022 , B2 = 15/02/2022
=DATE(YEAR(A1),MONTH(A1)+1,15)
what I am struggling with is the IF formula I need so that if the date in A1 is between the 1st and the 14th of the month (09/01/2022) the date in B1 would populate the 15th of the same month of the date in A1 (15/01/2022). IF the date in A1 is between the 15th and the last day of the month (20/01/2022) the date in B1 would populate the 15th of the following month (15/02/2022).
Any help achieving this would be greatly appreciated
>Solution :
Use:
=DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>=15),15)
The (DAY(A1)>=15) when true will add 1 if not it will add 0
