SOLVED

Formula to Set the date based on another cell date

Brass Contributor

Formula to Set the date based on another cell date

Hi there,

I need a formula to set the date to either the 10th or 25th based on when another date falls.

Anything from the 10th of any given month to the 24th in column A would have the date in column B be the 25th

Anything from the 25th to the 9th of any given month in column A would set the date in column B to the 10th.

Example:

A1= 01/01/2023

B1 = formula here to make this date be 01/10/2023

Example:

A1 = 01/15/2023

B1 = formula here to make this date be 01/25/2023

4 Replies
best response confirmed by LisaMarie1981 (Brass Contributor)
Solution

Re: Formula to Set the date based on another cell date

=IF(AND(DAY(A1)>=10,DAY(A1)<=24),

DATE(YEAR(A1),MONTH(A1),25),

IF(DAY(A1)<=10,

DATE(YEAR(A1),MONTH(A1),10),

DATE(YEAR(A1),MONTH(A1)+1,10)))

Does this return the intended output?

Re: Formula to Set the date based on another cell date

it worked in the cell I put the formula in, but when I try to drag the formula down it doesn't work?

Re: Formula to Set the date based on another cell date

I don't understand. Can you check if the intended result is returned in the attached file? It's the result shown in the screenshot of my first reply.

Re: Formula to Set the date based on another cell date

Sorry, please disregard, the date was wrong in the 2nd row so it didn't calculate, the formula worked fine once I fixed that. Thank you so much Oliver!!! 🙂
1 best response

Accepted Solutions
best response confirmed by LisaMarie1981 (Brass Contributor)
Solution

Re: Formula to Set the date based on another cell date

=IF(AND(DAY(A1)>=10,DAY(A1)<=24),

DATE(YEAR(A1),MONTH(A1),25),

IF(DAY(A1)<=10,

DATE(YEAR(A1),MONTH(A1),10),

DATE(YEAR(A1),MONTH(A1)+1,10)))

Does this return the intended output?