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

nkarlsvik

New Member
Joined
Jul 8, 2009
Messages
18
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!
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

JasonC71

Board Regular
Joined
Feb 10, 2008
Messages
159
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

New Member
Joined
Jul 8, 2009
Messages
18
Both does the trick using ; instead of ,

THANKS!!

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

nkarlsvik

New Member
Joined
Jul 8, 2009
Messages
18
Both does the trick using ; instead of ,

THANKS!!


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

Watch MrExcel Video

Forum statistics

Threads
1,122,710
Messages
5,597,702
Members
414,164
Latest member
ARTW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top