+ Reply to Thread
Results 1 to 4 of 4

=if(datedif...

  1. 04-29-2005, 06:43 AM #1
    loscherland is offline
    Registered User
    Join Date
    03-02-2004
    Posts
    10

    =if(datedif...

    Am using the following formula to show highlight expired records (Over certain date rance), & otherwise show how many days are left to expiry

    =IF(DATEDIF(TODAY(),M15,"d")<=0,"Expired",(DATEDIF(TODAY(),M15,"d")))

    Colum M contains the expiry date. If =DATEDIF returns less then or equal to 0 days, I want the cell to show "Expired" & otherwise show how many days are left to expiry

    It's worked fine for records which haven't yet expired but returns #NUM! for all expired records instead of "Expired"

    What have I done wrong???
    Reply With QuoteRegister To Reply
  2. 04-29-2005, 06:51 AM #2
    Domenic is offline
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Try...

    =IF(A1<=TODAY(),"Expired",DATEDIF(TODAY(),A1,"d"))

    OR

    =IF(A1<=TODAY(),"Expired",A1-TODAY())

    Hope this helps!
    Reply With QuoteRegister To Reply
  3. 04-29-2005, 07:01 AM #3
    loscherland is offline
    Registered User
    Join Date
    03-02-2004
    Posts
    10
    No that's not what I meant.

    Basically each record has an Activation Date. The expiry date is this activation date + 75 days. So i've created a colum to to show the expiry date for each record.
    Eg.

    A1 (Activation Date) B1 (Expiry Date) M1 (Show "Expired or Days left to Expiry)
    07-02-05 John Smith 23-04-05 (=A1+75) {Dif between Exp & TODAY > 75, Expired}
    18-03-05 Mary Smith 01-06-05 (=A1+75) {Dif between Exp & TODAY < 75, show days left to expiry ~ 33}

    In column N I want the forumula to calculate if this Expiry date in Column M is greater then todays date (Expired) & if not, how many days remain until expiry
    Last edited by loscherland; 04-29-2005 at 07:09 AM.
    Reply With QuoteRegister To Reply
  4. 04-29-2005, 07:29 AM #4
    Domenic is offline
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Actually, the reference to A1 should have been M1...

    =IF(M1<=TODAY(),"Expired",M1-TODAY())

    Hope this helps!
    Last edited by Domenic; 04-29-2005 at 07:33 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1