Automatic Date

[Jlopez0320]

Hello!

I am new to the community and Excel for anything more than hat dont require formulas. I am creating a document to track clients that I have to contact for work and want to have the "Next Follow Up" column (K) auto populate a date that is either one week past the "Referral Follow up" (I) or "Last Client Contact" (J)

I currently have this formula under column J, but any effort to have 'If K is blank, leave J blank' has been unsuccessful and gives me the date 1/7/1900 if there is nothing in column K.

=IF(J2=" "," ", DATE(YEAR(J2), MONTH(J2), DAY(J2)+7))

This is what the sheet can look like. Referral follow-up can have up to three dates, as we will have three touchpoints before exiting a client from the program.

Also! I eventually want to change the conditional formatting of the "Next Follow-up" column to change the fill color as we approach the date. Would that affect the conditional formatting of the rows? Is there an easier way to do this? Thank you again for all the help!

 

[awoohaw]

Formula for column K:
Why do you have two values in the first cell in the 2nd row? That can be difficult to work with. So, please explain your reasoning with how you input data into columns I and J? Are you entering DATES as a date value and not as text?

=MAX(I2,J2)+7

As far a conditional formatting, how many days warning do you want to change the color?
How big is the workbook? Will you need the conditional formatting in 1000s of cells?

 

[Cubist]

Try:

Book1
	I	J	K
1	Referral Follow up	Last Client Contact	Next Follow Up
2	6/11/2024, 6/17/2024		6/24/2024
3	6/4/2024	6/11/2024	6/11/2024
4
5	6/10/2024		6/17/2024
6		5/6/2024	5/13/2024
Sheet11

Cell Formulas
Range	Formula
K2:K6	K2	=IF(COUNTIF(I2:J2,"<>")>0,MAX(IFERROR(--TEXTSPLIT(I2,","&CHAR(10)),J2),0)+7,"")

 

[Cubist]

The IFERROR parameter was out of order above. Redo.

Book1
	I	J	K
1	Referral Follow up	Last Client Contact	Next Follow Up
2	6/11/2024, 6/17/2024		6/24/2024
3	6/3/2024	6/11/2024	6/18/2024
4
5	6/10/2024	7/10/2024	7/17/2024
6		5/6/2024	5/13/2024
Sheet12

Cell Formulas
Range	Formula
K2:K6	K2	=IF(COUNTIF(I2:J2,"<>")>0,MAX(IFERROR(--TEXTSPLIT(I2,","&CHAR(10)),0),--J2)+7,"")

 

[Jlopez0320]

The IFERROR parameter was out of order above. Redo.

Book1
	I	J	K
1	Referral Follow up	Last Client Contact	Next Follow Up
2	6/11/2024, 6/17/2024		6/24/2024
3	6/3/2024	6/11/2024	6/18/2024
4
5	6/10/2024	7/10/2024	7/17/2024
6		5/6/2024	5/13/2024
Sheet12

Cell Formulas
Range	Formula
K2:K6	K2	=IF(COUNTIF(I2:J2,"<>")>0,MAX(IFERROR(--TEXTSPLIT(I2,","&CHAR(10)),0),--J2)+7,"")

Thank you this did it!

 

[Jlopez0320]

Formula for column K:
Why do you have two values in the first cell in the 2nd row? That can be difficult to work with. So, please explain your reasoning with how you input data into columns I and J? Are you entering DATES as a date value and not as text?

=MAX(I2,J2)+7

As far a conditional formatting, how many days warning do you want to change the color?
How big is the workbook? Will you need the conditional formatting in 1000s of cells?

There are two values because we need to have three touchpoints with a client before they are removed from this log. That column would be the the best place to track it if possible?


As for conditional formatting, I was considering three days and a different color as it gets closer to the next contact date