3 IF statements for a cell??

locolindo

New Member
Joined
Dec 5, 2014
Messages
12
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:

Column AColumn BColumn C
Admission DateDischarge DateLenght of Stay
NO DATADATA"No Admission Date"
DATANO DATA"Ongoing"
NO DATANO DATA"No Data for Admission or Discharge"
DATADATACalculates B-A (easy!!!)

<tbody>
</tbody>

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

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Using IF statements:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Admission Date</td><td style=";">Discharge Date</td><td style=";">Length of Stay</td><td style=";">Length of Stay</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">6/1/2018</td><td style=";">No Admission Date</td><td style=";">No Admission Date</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">5/15/2018</td><td style="text-align: right;;"></td><td style=";">Ongoing</td><td style=";">Ongoing</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">No Data for Admission and Discharge</td><td style=";">No Data for Admission and Discharge</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">5/15/2018</td><td style="text-align: right;;">6/1/2018</td><td style="text-align: right;;">17</td><td style="text-align: right;;">18</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet73</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">A2="",B2=""</font>),"No Data for Admission and Discharge",IF(<font color="Red">A2="","No Admission Date",IF(<font color="Green">B2="","Ongoing",B2-A2</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">A2="",B2=""</font>),"No Data for Admission and Discharge",IF(<font color="Red">A2="","No Admission Date",IF(<font color="Green">B2="","Ongoing",B2-A2+1</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

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

Either way, formula copied down.
 
Last edited:

Forum statistics

Threads
1,082,587
Messages
5,366,486
Members
400,894
Latest member
frog9000

Some videos you may like

This Week's Hot Topics

Top