+ Reply to Thread
Results 1 to 6 of 6

IF statement together with a couple of functions

  1. #1
    Registered User
    Join Date
    04-20-2011
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2013
    Posts
    54

    Lightbulb IF statement together with a couple of functions

    Hi to all excel guru's in here :-)

    I have a real headache thinking about how I can come up with a genious formula to improve/streamline my excel sheet.

    In the attachment you will see my setup. What is important to focus on is column F. The information listed in column F is the date when a specific persons crane certificate expires. For example cell F3 shows that Domingo N General's Hydralift certificate expires on the 18.06.2012.

    What I would like to have set up is a system that automatically picks out those certificates that expire within 3 months or have expired. This system will be able to provide me with valuable information because I can better plan refresher training so no certificates expire. In an ideal world the system should assist me in better planning, minimize human errors which in return means more time for other tasks.

    I hope there is an extremely talented excel individual out there who can help me with my query. What I have thought of so far is to use the IF statement together with LOOKUP but I just dont know how to do the DATE thing.

    Thank you in advance.
    Attached Files Attached Files
    Last edited by Kagesen; 04-24-2011 at 02:25 AM. Reason: Attached excel file

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: IF statement together with a couple of functions

    Hi

    why post an image when you need a spreadsheet?

  3. #3
    Registered User
    Join Date
    04-20-2011
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2013
    Posts
    54

    Re: IF statement together with a couple of functions

    You are totally correct arthurbr - I have now attached a copy of my excel sheet. Please assist me if possible and thank you for spotting my error.

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: IF statement together with a couple of functions

    I have applied Conditional Formatting to columns F and G.
    When the date has arrived or passed the cell turns red and when the date is withing 3 months of today's date the cell turns yellow.

    The CF formulas are:
    =AND(F3<>"",TODAY()>=F3)
    and
    =AND(F3<>"",TODAY()>=EDATE(F3,-3))

    I had to change your date formats to one that my comp recognizes so change them back on yours.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-20-2011
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2013
    Posts
    54

    Re: IF statement together with a couple of functions

    Hi Cutter.

    This looks great. You are brilliant!

    I have not worked with that Conditional Formatting before but I want to try and do what you have written above. I knew there was a very bright head in here somewhere - THANKS Cutter.

  6. #6
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: IF statement together with a couple of functions

    You're welcome.
    Thanks for the "scales tap".

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1