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]

Maybe

=max($C:$C)+42

 

[etaf]

=IF(C12<>"",$C12+42,"")

 

[SaraWitch]

Hi Gaz_chops. Thank you for the MAX formula. Can this be tweaked to not calculate if there are any blanks in column C (I only want the deadline on the latest date once all have been entered)? (Can't do an array formula as I have merged cells!)

 

[gaz_chops]

I think you need to post a sample of your data and what results you are trying to get. Use the XL2BB option.

 

[SaraWitch]

Hi, I've not used XL2BB before, so I hope I've done this right!

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)
18/05/2020
06/06/2020			18/07/2020
06/06/2020
01/06/2020			26/08/2020
15/07/2020

 

[gaz_chops]

Still not clear to me what you want to achieve.

I take it the Response Target dates are what you are after, you said earlier if there are blanks in Col C, which i assume is the Date Rec'd, then don't calculate!
What triggers the calc, is it if the 1st & Last cell adjacent to the merged cell have a date?

 

[gaz_chops]

If I am correct, then is there something in Col A or B that can be used to Identify that the cells are grouped together! Also, you really need to remove the merged cells, they are only going to cause you problems!

In this example I can use Col A to find the last cell in the group, assuming that is always the latest!

 

[SaraWitch]

Sorry, yes. So column A is blank, column B is the reference number, column C is the Date Received and Column D is the Response Target Date. At the moment the formula calculates the Response Target date from the last Date Received line in each section. However, if there is more than two lines, the third Date Received could be after the last Date Received and the Response Target date is calculated from the latest date in the range. Hope this is clearer...?

	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			26/08/2020
		15/07/2020

 

[gaz_chops]

I cannot see a way of doing this with a formula if you keep the merged cells. Someone else may be able to, or it can probably be done with VBA.

If you can remove the merged cells, which I seriously think you should, then try

=IF(OR(B3="",B3=B4,C3=""),"",C3+42
enter in Col D and copy down