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

CodePudding user response：

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