Results 1 to 6 of 6

Thread: Excel help

  1. #1
    Sexiest Hexus user? quite possibly Russ's Avatar
    Join Date
    Jul 2003
    Location
    North Norfolk
    Posts
    5,200
    Thanks
    11
    Thanked
    69 times in 44 posts
    • Russ's system
      • Motherboard:
      • Apple Logic Board
      • CPU:
      • Core i5 2.8GHZ 8MB Cache
      • Memory:
      • 2x2GB
      • Storage:
      • 1TB
      • Graphics card(s):
      • ATI 5750
      • Case:
      • iMac
      • Operating System:
      • Mac OS X Snow Leopard
      • Monitor(s):
      • 27" iMac
      • Internet:
      • 2mb(on a good day)

    Excel help

    hi guys

    doing a spreadsheet for work, and just wondered if this formula was possible.

    i want it to say a date, and in x amount of years, i want that date to change to the word exprired.

    i.e. 29/11/07 - on the 29/11/10 (where x = 3 ) read Expired

    can this be done?
    Gamertag - Russonf (xbox and ps3)

  2. #2
    SiM
    SiM is offline
    Senior Member
    Join Date
    Apr 2006
    Location
    London
    Posts
    7,787
    Thanks
    300
    Thanked
    633 times in 422 posts
    • SiM's system
      • Motherboard:
      • P5K Premium
      • CPU:
      • Q6600
      • Memory:
      • 8GB PC2-6400 OCZ ReaperX + Platinum
      • Storage:
      • 3 x 320gb HD322HJ single platter in Raid 0
      • Graphics card(s):
      • PNY GTX285
      • PSU:
      • Corsair TX650W
      • Case:
      • Antec 1200
      • Monitor(s):
      • 2407-HC

    Re: Excel help

    Do you mean you want to display the date until the expiry date?
    for 29\11\10 its this
    if(today()=40511,"expired",40511)

  3. #3
    SiM
    SiM is offline
    Senior Member
    Join Date
    Apr 2006
    Location
    London
    Posts
    7,787
    Thanks
    300
    Thanked
    633 times in 422 posts
    • SiM's system
      • Motherboard:
      • P5K Premium
      • CPU:
      • Q6600
      • Memory:
      • 8GB PC2-6400 OCZ ReaperX + Platinum
      • Storage:
      • 3 x 320gb HD322HJ single platter in Raid 0
      • Graphics card(s):
      • PNY GTX285
      • PSU:
      • Corsair TX650W
      • Case:
      • Antec 1200
      • Monitor(s):
      • 2407-HC

    Re: Excel help

    Oh I think I understand what you mean now...
    You will need to input either the start date and x value or just the end date - unless you want to write a vba function, then you could get it to save the date that you ran the function, then it will only need x value. The method above has the end date hard coded into the formula...

    if(today()=start_date+(365)*x,"expired",40511)
    need to input start_date and x value with this

  4. Received thanks from:

    Russ (29-11-2007)

  5. #4
    Sexiest Hexus user? quite possibly Russ's Avatar
    Join Date
    Jul 2003
    Location
    North Norfolk
    Posts
    5,200
    Thanks
    11
    Thanked
    69 times in 44 posts
    • Russ's system
      • Motherboard:
      • Apple Logic Board
      • CPU:
      • Core i5 2.8GHZ 8MB Cache
      • Memory:
      • 2x2GB
      • Storage:
      • 1TB
      • Graphics card(s):
      • ATI 5750
      • Case:
      • iMac
      • Operating System:
      • Mac OS X Snow Leopard
      • Monitor(s):
      • 27" iMac
      • Internet:
      • 2mb(on a good day)

    Re: Excel help

    sent you a pm sim
    Gamertag - Russonf (xbox and ps3)

  6. #5
    Mostly Me Lucio's Avatar
    Join Date
    Mar 2007
    Location
    Tring
    Posts
    5,163
    Thanks
    443
    Thanked
    448 times in 351 posts
    • Lucio's system
      • Motherboard:
      • Gigabyte GA-970A-UD3P
      • CPU:
      • AMD FX-6350 with Cooler Master Seldon 240
      • Memory:
      • 2x4GB Corsair DDR3 Vengeance
      • Storage:
      • 128GB Toshiba, 2.5" SSD, 1TB WD Blue WD10EZEX, 500GB Seagate Baracuda 7200.11
      • Graphics card(s):
      • Sapphire R9 270X 4GB
      • PSU:
      • 600W Silverstone Strider SST-ST60F
      • Case:
      • Cooler Master HAF XB
      • Operating System:
      • Windows 8.1 64Bit
      • Monitor(s):
      • Samsung 2032BW, 1680 x 1050
      • Internet:
      • 16Mb Plusnet

    Re: Excel help

    It's slightly more accurate to use the following (only because it covers leap years TBH)

    IF(NOW()>date,IF(YEARFRAC(NOW(),date)>=3,"Expired",date),date)

    The reason for two IF, formula's is it covers a case when a future date is entered and is so far in the future, it'd trigger the "Expired" message

    Replace "date" with cell reference for your date column and you can replace the 3 with whichever number you want to expire the column on.

  7. Received thanks from:

    Russ (29-11-2007)

  8. #6
    Sexiest Hexus user? quite possibly Russ's Avatar
    Join Date
    Jul 2003
    Location
    North Norfolk
    Posts
    5,200
    Thanks
    11
    Thanked
    69 times in 44 posts
    • Russ's system
      • Motherboard:
      • Apple Logic Board
      • CPU:
      • Core i5 2.8GHZ 8MB Cache
      • Memory:
      • 2x2GB
      • Storage:
      • 1TB
      • Graphics card(s):
      • ATI 5750
      • Case:
      • iMac
      • Operating System:
      • Mac OS X Snow Leopard
      • Monitor(s):
      • 27" iMac
      • Internet:
      • 2mb(on a good day)

    Re: Excel help

    sorted, cheers sim and lucio
    Gamertag - Russonf (xbox and ps3)

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. .NET Reading Multiple Excel Files
    By Shadow_101 in forum Software
    Replies: 5
    Last Post: 23-11-2007, 03:22 PM
  2. Excel + Database + Automated
    By Kezzer in forum Software
    Replies: 4
    Last Post: 29-03-2007, 04:54 PM
  3. Excel windows
    By Steve in forum Software
    Replies: 4
    Last Post: 25-07-2006, 12:44 PM
  4. M$ Excel masters..
    By XA04 in forum Help! Quick Relief From Tech Headaches
    Replies: 9
    Last Post: 19-09-2005, 10:48 AM
  5. Replies: 6
    Last Post: 12-08-2005, 08:50 AM

Posting Permissions

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