Calculate Deadline Date Based On Latest Date In Previous Column

[SaraWitch]

Hello everyone.

I would like to create a deadline date based on the latest date in the previous column. I have an existing formula for two entries (=IF($C12>0,$C12+42,""), but this doesn't work for more than two dates when the deadline is calculated 42 days on from the latest date. I have tried the MAX formula to put in another column, but can't work out how to not return a result if any cells are blank.

• Column C = dates received
• Column F = deadline date (42 days after latest date in column C (and is where current formula of =IF($C>0,$C12+42,"" is))

One formula in column F would be ideal, if possible!

Ta muchly, folks!
Sara

 

[gaz_chops]

This appears to work if you keep Col B as merged cells, but remove the merge from Col D

=IF(OR(B21<>"",C21=""),"",C21+42)

 

[SaraWitch]

Thank you. Although your original formula, "=MAX($C:$C)+42" works well. I just need this tweaked that if any cells in column C are blank, don't calculate (only when all the dates are received, does column D give a Response Target Date). I did try "=IF($C13:$C17>0,MAX($C13:$C17)+42,"")", but that's not working... e.g.

	MEx Ref No.	Date Received (if 2-way MEx, enter 2nd details in third line of section)			Response Target (date will auto-populate when last application date received entered)
	0001	18/05/2020
	0002	06/06/2020			18/07/2020
		06/06/2020
	0003	01/06/2020			08/09/2020
		28/07/2020
		15/07/2020

 

[SaraWitch]

Hello. I've worked it out!! So, I've used your original MAX formula and set a conditional formatting of "=COUNTBLANK ($C:$C)" and set the font colour to the row colour. Works a treat - thank you for your help...

 

[gaz_chops]

You're welcome, I'm surprised it works with merged cells! But if it does great.