Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am using the DAYS360 formula to count how many days since last activity from a predefined date


The R Column reports on last activity date, whilst the the X1 cell contains the current date.

All is good except for when the there is no date in Column R. The formula then spits out an amount of days of 40753 (this increases day by day).

How to i add to my above formula so that if the value is >= 40753 the value returned is 0 or NO ACTIVITY?

share|improve this question
Note that DAYS360 is an accounting function which treats each month as 30 days (as the name suggests) so it doesn't give an accurate count of days in all cases, e.g. =DAYS360(DATE(2013,3,30),DATE(2013,4,1)) gives a result of 1 - but those dates are 2 days apart - if you want an accurate number of days between two dates just subtract the earlier from the later, e.g. in your case =IF($R15="","",$X$1-$R15) – barry houdini Mar 13 '13 at 11:59
up vote 1 down vote accepted

Alternatively, only calculate if ColumnR value exists: =IF(ISBLANK($R15),0,DAYS360($R15,$X$1))

share|improve this answer
Excellent pnuts!! i used the ISBLANK version and displayed "NO ACTIVITY", instead of 0. Now when i pivot this data the value in the pivot displays as DIV/0, how can i also make the this read no activity or 0? – Rohan F Mar 13 '13 at 3:01
Have you tried checking "For error vales show" and inserting "NO ACTIVITY" (PivotTable Options>Layout & Format -Format>OK)? [It is not very easy to advise you here without knowing what you are pivoting.] – pnuts Mar 13 '13 at 8:21

You can use an IF

=IF(DAYS360($R15,$X$1)>=40753, 0, DAYS360($R15,$X$1))
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .