3 IF statements for a cell??

[locolindo]

Hi There,
I have a table with hospital admission date (column A), discharge date (column B), and a third column that calculates the duration of stay (column C).
Sometimes, people forget to enter data for either the admission date (column A), or the discharge date (column B), or both (columns A & B)!!!
I would like to create a function for column C that does the following:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Column A[/TD]
[TD="align: center"]Column B[/TD]
[TD="align: center"]Column C[/TD]
[/TR]
[TR]
[TD]Admission Date[/TD]
[TD]Discharge Date[/TD]
[TD]Lenght of Stay[/TD]
[/TR]
[TR]
[TD]NO DATA[/TD]
[TD]DATA[/TD]
[TD]"No Admission Date"[/TD]
[/TR]
[TR]
[TD]DATA[/TD]
[TD]NO DATA[/TD]
[TD]"Ongoing"[/TD]
[/TR]
[TR]
[TD]NO DATA[/TD]
[TD]NO DATA[/TD]
[TD]"No Data for Admission or Discharge"[/TD]
[/TR]
[TR]
[TD]DATA[/TD]
[TD]DATA[/TD]
[TD]Calculates B-A (easy!!!)[/TD]
[/TR]
</tbody>[/TABLE]

Can such a function be done all for one cell?
Thank you!!!
Locolindo

 

[jtakw]

Hi,

Using IF statements:

Book1
	A	B	C	D
1	Admission Date	Discharge Date	Length of Stay	Length of Stay
2		6/1/2018	No Admission Date	No Admission Date
3	5/15/2018		Ongoing	Ongoing
4			No Data for Admission and Discharge	No Data for Admission and Discharge
5	5/15/2018	6/1/2018	17	18
Sheet73

Cell Formulas
Range	Formula
C2	=IF(AND(A2="",B2=""),"No Data for Admission and Discharge",IF(A2="","No Admission Date",IF(B2="","Ongoing",B2-A2)))
D2	=IF(AND(A2="",B2=""),"No Data for Admission and Discharge",IF(A2="","No Admission Date",IF(B2="","Ongoing",B2-A2+1)))

If the length of stay should be Admission/Discharge Day Inclusive, use D2 formula, otherwise use C2 formula.

Either way, formula copied down.

 

[locolindo]

Thank you jtakw!

 

[jtakw]

You're welcome.