Cells will become 0 automatically without changing formula

Hi All

I need help . Can or not if i use this formula =IF(OR(B5="",C5=""),0,DAYS360($B5,$C5,TRUE)+1) , If unpaid leave, half-pay leave and study leave, total days cells will become 0 automatically without changing formula

Hello,

Not sure, what you are referring as without changing formula. See if this helps. In E5, paste the below formula and copy down

Please Login or Register  to view this content.

Note: Study Leave has a space in the end & half pay leaf has a typo i guess. Both needs to be corrected if needed.

how about if there is additional 1 columns. If unpaid leave, half-pay leave and study leave, total column will be 360 and total day colum will become 0

Just reverse the true, false value in sixth sense formula,

=IF(OR(B5="",C5="",ISNUMBER(MATCH(D5,REASON,0))),DAYS360($B5,$C5,TRUE)+1,0) - in E5 and drag down

=IF(OR(B5="",C5="",ISNUMBER(MATCH(D5,REASON,0))),0,DAYS360($B5,$C5,TRUE)+1) - IN F5 and drag down

Originally Posted by elayaz

Just reverse the true, false value in sixth sense formula,

=IF(OR(B5="",C5="",ISNUMBER(MATCH(D5,REASON,0))),DAYS360($B5,$C5,TRUE)+1,0) - in E5 and drag down

Why cells B5 & C5 blank at cells E5 have 1 not 0

That's because the original formula which you don't want to change had DAYS360 +1

Try this altered sixth sense formula in E5 and drag down

Please Login or Register  to view this content.

Originally Posted by elayaz

That's because the original formula which you don't want to change had DAYS360 +1

Try this altered sixth sense formula in E5 and drag down

Please Login or Register  to view this content.

if i use this formula 1/1/2013 - 2/7/2013 in e5 181 day. i want 182 day because i count 1 month average 30 day. 1/1/2013 - 30/6/2013 = 180 day, 1/7/2013 - 2/7/2013 = 2 day. All day is 182.

Originally Posted by :) Sixthsense :)

Try this...

Formula:

Please Login or Register  to view this content.

how to include word automatically in E5. Example : 182 day

Sixth sense formula should work for you. Else wrap another if function in the original sixth sense formula

Please Login or Register  to view this content.

Can or not in excel fix date based on year

A1 (YEAR) 2004
B1 (FROM) 1/1
C1 (TO) 2/7

I key in only 1/1 or 2/7 and the date change 1/1/2004 or 2/7/2004 based on year at cells A1

Originally Posted by elayaz

sixth sense formula should work for you. Else wrap another if function in the original sixth sense formula

Please Login or Register  to view this content.

formula that is correct