Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Formula to Set the date based on another cell date

Brass Contributor

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

 

Thanks in advance!

 

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

@LisaMarie1981 

=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?

dates.png

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

@LisaMarie1981 

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.

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

@LisaMarie1981 

=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?

dates.png

View solution in original post