SOLVED

Change value of a cell based on the date in another cell.

Copper Contributor

Hello,

 

I am trying to set up an Excel sheet for work. In column E, there is a date for each row. If that cell's date is less than or equal to one year from today's date, I need it to change the value of column C to "1". If it is greater than one year away, column C needs to be "2"

 

Is this possible to do?

3 Replies
best response confirmed by Justalex_BTW (Copper Contributor)
Solution

@Justalex_BTW 

In C2:

 

=IF(E2="", "", IF(E2<=EDATE(TODAY(), 12), 1, 2))

 

Fill down.

@Hans Vogelaar 

 

Thank you! That works perfectly. If you can, I need help with another situation.

 

In column D, I have a completion date. In column F is a due date that's 90 days before the date in column D. In column K is a different due date that's 30 days before the date in column D.

 

Is it possible to have a formula that changes the values for columns F and K to their respective dates based on the date of column D?

@Justalex_BTW 

In F2:  =IF(D2="", "", D2-90)

In K2: =IF(D2="", "", D2-30)

 

Fill down.

1 best response

Accepted Solutions
best response confirmed by Justalex_BTW (Copper Contributor)
Solution

@Justalex_BTW 

In C2:

 

=IF(E2="", "", IF(E2<=EDATE(TODAY(), 12), 1, 2))

 

Fill down.

View solution in original post