I have big issue in excel formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1127331%22%20slang%3D%22en-US%22%3EI%20have%20big%20issue%20in%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1127331%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EGood%20evening%2C%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20have%20big%20issue%20with%20excel.%20I%20want%20to%20calculate%20number%20of%20days%20%22long%20stay%20patient%22%20from%20admission%20date%20based%20on%203%20criteria.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E1.%20if%20discharge%20patient%20not%20today%2C%20then%20calculate%20number%20of%20days%20%22%20long%20stay%20patient%22%20from%20admission%20till%20discharged%20date.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E2.%20if%20expired%20patient%20not%20today%2C%20then%20calculate%20number%20of%20days%20%22%20long%20stay%20patient%22%20from%20admission%20till%20expired%20date.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E3.%20if%20not%20discharge%20or%20expired%2C%20then%20calculate%20number%20of%20days%20%22%20long%20stay%20patient%22%20from%20admission%20till%20today.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EPlease%20see%20excel%20sheet%20in%20attachment%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks%20a%20lot%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1127331%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1127821%22%20slang%3D%22en-US%22%3ERe%3A%20I%20have%20big%20issue%20in%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1127821%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F531439%22%20target%3D%22_blank%22%3E%40alrasi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DMAX(0%2CIFERROR(1%2F(1%2F(L4%2BM4))%2CTODAY())-E4)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1127840%22%20slang%3D%22en-US%22%3ERe%3A%20I%20have%20big%20issue%20in%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1127840%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F531439%22%20target%3D%22_blank%22%3E%40alrasi%3C%2FA%3E%26nbsp%3B%20%26nbsp%3Btry%20this...%26nbsp%3B%20(in%20cell%20G6)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(ISBLANK(%24L6)%2CISBLANK(%24M6))%2C%22%22%2CIF(ISBLANK(%24M6)%2CDAYS(%24L6%2C%24E6)%2CDAYS(%24M6%2C%24E6)))%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Good evening,

 

I have big issue with excel. I want to calculate number of days "long stay patient" from admission date based on 3 criteria.

 

1. if discharge patient not today, then calculate number of days " long stay patient" from admission till discharged date.

 

2. if expired patient not today, then calculate number of days " long stay patient" from admission till expired date.

 

3. if not discharge or expired, then calculate number of days " long stay patient" from admission till today.

 

Please see excel sheet in attachment

 

Thanks a lot

2 Replies
Highlighted

@alrasi 

Try this:

=MAX(0,IFERROR(1/(1/(L4+M4)),TODAY())-E4)
Highlighted

@alrasi   try this...  (in cell G6)

 

=IF(AND(ISBLANK($L6),ISBLANK($M6)),"",IF(ISBLANK($M6),DAYS($L6,$E6),DAYS($M6,$E6)))