Return 0 if cell value is <0 and >365 ?

[nkarlsvik]

Hi! I know this might be a beginners question, but I really ned help with this one.

I have a spreadsheet that is suppose to calc the amount of days between two dates, only problem is that when one of the dates is missing, the cell returns the numbers between 01-01-1900 and the date provided.

I use two cells; one that actually recieves the high number ca +/-40000 and another cell that returns 0 IF the number in the first cell returns a number below 0 or over 365. Only problem is that it doesnt work properly..

Heres the formula im trying to use: =IF(I10<365;I10;0+IF(I10>0;I10;0))

its basically a combined formel of these:
=IF(I10<365;I10;0)
=IF(I10>0;I10;0)

Im using Excel 2007.

Thanks!

 

[doofusboy]

=if(or(i10<0,i10>365),0,i10)

 

[JasonC71]

Are you simply trying to find the number of days between two dates? If so, why not just subtract one from the other...

As an example...say A1 and B1 contain two random dates...in C1 we could put:

=IF(OR(A1="",B2=""),0,ABS(A1-B2))

 

[nkarlsvik]

Both does the trick using ; instead of ,

THANKS!!

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
=IF(OR(I10<0;I10>365);0;I10)<o></o>
<o> </o>
=IF(OR(A1="";B2="");0;ABS(A1-B2))<o></o>

 

[nkarlsvik]

Both does the trick using ; instead of ,

THANKS!!


<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
=IF(OR(I10<0;I10>365);0;I10)<o></o>
<o></o>
=IF(OR(A1="";B2="");0;ABS(A1-B2))<o></o>