Question about Date formula

[not7here]

Hi all!

Long time lurker, first time poster. Wondering if there's any advice on the following issue. I have a patient tracker (healthcare for Veterans) and I'm looking to calculate a date. A patient will have an ADMISSION DATE and for the first YEAR (365 days) I need to complete a treatment plan every three months. Once the Admission date is more than a year ago, then I need to calculate a treatment plan date that is every 6 months. So For instance:

Admission Date (column A)	Last Treatment Plan Completed (column B)	Next Treatment Plan due (column C)
01/01/22	01/01/22	07/01/22
08/01/21	08/01/22	02/01/23

So in this case, the user will manually put in the admission date and the last treatment plan they did. If its been less than a year since admission, the "Next" one they do will be calculated 3 months from their last completed plan. If its been more than a year, it will be calculated 6 months from their last completed plan.

Any advice? I can't wrap my head around this one.

Thank you!
Chris

 

[KRice]

Try this...

Book1
	A	B	C
1	Admission Date	Last Treatment Plan Completed	Next Treatment Plan Due
2	1/1/2022	1/1/2022	4/1/2022
3	8/1/2021	8/1/2022	2/1/2023
Sheet1

Cell Formulas
Range	Formula
C2:C3	C2	=IF(B2<EDATE(A2,12),EDATE(B2,3),EDATE(B2,6))

 

[not7here]

Try this...

Book1
	A	B	C
1	Admission Date	Last Treatment Plan Completed	Next Treatment Plan Due
2	1/1/2022	1/1/2022	4/1/2022
3	8/1/2021	8/1/2022	2/1/2023
Sheet1

Cell Formulas
Range	Formula
C2:C3	C2	=IF(B2<EDATE(A2,12),EDATE(B2,3),EDATE(B2,6))

That's fantastic thank you! Last question I swear...

If B2 (Last treatment plan) cell is empty, can I make C2 empty as well? I've used a different IF function for that but it doesn't seem to work here. Here is the code I have which keeps getting an error:

=IF(B2<>"",=IF(B2<EDATE(A2,12),EDATE(B2,3),EDATE(B2,6)),"")

 

[KRice]

Sure...I would do something almost like that...

MrExcel_20220808.xlsx
	A	B	C
1	Admission Date	Last Treatment Plan Completed	Next Treatment Plan Due
2	1/1/2022	1/1/2022	4/1/2022
3	8/1/2021	8/1/2022	2/1/2023
4	1/15/2022
5	1/15/2022	4/15/2022	7/15/2022
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=IF(B2="","",IF(B2<EDATE(A2,12),EDATE(B2,3),EDATE(B2,6)))

I believe your formula would work fine too if you deleted the = sign in front of the 2nd IF.

 

[not7here]

Sure...I would do something almost like that...

MrExcel_20220808.xlsx
	A	B	C
1	Admission Date	Last Treatment Plan Completed	Next Treatment Plan Due
2	1/1/2022	1/1/2022	4/1/2022
3	8/1/2021	8/1/2022	2/1/2023
4	1/15/2022
5	1/15/2022	4/15/2022	7/15/2022
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=IF(B2="","",IF(B2<EDATE(A2,12),EDATE(B2,3),EDATE(B2,6)))

I believe your formula would work fine too if you deleted the = sign in front of the 2nd IF.

Thank you! Tried it though and it doesn't work when the second = is paired with the second IF. And when I remove that = sign then there's a #SPILL! error on all entries in the column.

My code is:

=IF([Last Tx Plan]="","",IF([@[Last Tx Plan]]<EDATE([@[Admit Date]],12),EDATE([@[Last Tx Plan]],3),EDATE([@[Last Tx Plan]],6)))

Where:

A2 = [Admit Date]
B2 = [Last Tx Plan]
C2 = the target cell

 

[KRice]

Oh…you’re using structured references. Place an @ in front of the first [Last Tx Plan] to consider only the cell on that row.

=IF(@[Last Tx Plan]="","",IF([@[Last Tx Plan]]<EDATE([@[Admit Date]],12),EDATE([@[Last Tx Plan]],3),EDATE([@[Last Tx Plan]],6)))

 

[not7here]

Oh…you’re using structured references. Place an @ in front of the first [Last Tx Plan] to consider only the cell on that row.

=IF(@[Last Tx Plan]="","",IF([@[Last Tx Plan]]<EDATE([@[Admit Date]],12),EDATE([@[Last Tx Plan]],3),EDATE([@[Last Tx Plan]],6)))

Works! This community is amazing. Thank you.

 

[KRice]

I thought you might find this interesting. With structured references and a desire to deliver the results into the same table, you'll need to use the @ symbols to fix the references to the row being evaluated (see upper table). If you try to use arrays (so we're looking at entire columns in the table), and again trying to deliver the results with a single formula back into the same table, you'll get a SPILL error (see lower table). However, if you use the same formula (the array version in the lower table) and you want the results external to the table, you can enter the formula just once (here in cell E20) and the results will spill down. Excel does not have the ability to spill results within a formal table. Also note that when using some older functions (like EDATE), in order to coax them into handling arrays, the unary plus operator is used in front of the array fed into the function.

MrExcel_20220808.xlsx
	A	B	C	D	E
12	Admit Date	Last Tx Plan	Next Tx Plan Due
13	1/1/2022	1/1/2022	4/1/2022
14	8/1/2021	8/1/2022	2/1/2023
15	1/15/2022
16	1/15/2022	4/15/2022	7/15/2022
17
18
19	Admit Date	Last Tx Plan	Next Tx Plan Due		Next Tx Plan (external to data table)
20	1/1/2022	1/1/2022	#SPILL!		4/1/2022
21	8/1/2021	8/1/2022	#SPILL!		2/1/2023
22	1/15/2022		#SPILL!
23	1/15/2022	4/15/2022	#SPILL!		7/15/2022
Sheet1

Cell Formulas
Range	Formula
C13:C16	C13	=IF([@[Last Tx Plan]]="","",IF([@[Last Tx Plan]]<EDATE([@[Admit Date]],12),EDATE([@[Last Tx Plan]],3),EDATE([@[Last Tx Plan]],6)))
E20:E23	E20	=IF(Table13[Last Tx Plan]="","",IF(Table13[Last Tx Plan]<EDATE(+Table13[Admit Date],12),EDATE(+Table13[Last Tx Plan],3),EDATE(+Table13[Last Tx Plan],6)))
C20:C23	C20	=IF([Last Tx Plan]="","",IF([Last Tx Plan]<EDATE(+[Admit Date],12),EDATE(+[Last Tx Plan],3),EDATE(+[Last Tx Plan],6)))
Dynamic array formulas.