+ Reply to Thread
Results 1 to 5 of 5

Function within and function x4 - Display text when date is less than 30 from today etc

  1. #1
    Registered User
    Join Date
    08-24-2016
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    2

    Question Function within and function x4 - Display text when date is less than 30 from today etc

    Hi All,

    I hope you're well .

    I've been driving myself crazy trying get this formula to work and I've finally given up and have to admit defeat. I think I'm almost there though?

    To start from the beginning:

    I have a 'last completed' date entered in cell F2 - displays as '17-Mar-16'.

    As this is due to be completed annually, I've hidden column G that states '1' in cell G2.

    In cell H2, is the following formula '=IF(ISBLANK(F2),"",DATE(YEAR(F2)+G2,MONTH(F2),DAY(F2)))'. This will show a blank cell if no data in cell F2 and a year from the date if it has been entered in cell F2.

    I now want to create a formula in column I that will tell me certain words if the date in cell H2 meets the following criteria:

    If the date displayed in cell H2 is 1 month prior to today's date, I want the text in the cell to display as "Due in 1 Month"
    If the date displayed in cell H2 is 2 months prior to today's date, I want the text in the cell to display as "Due in 2 Months"
    If the date displayed in cell H2 is anything more than 2 months prior to today's date, I want the text in the cell to display as "Completed"
    If the date displayed in cell H2 is equal to today's date or older then I wanted the text in the cell to display as "Expired"

    I so far have the following formula:

    =IF(ISBLANK(F2),"",IF(H2<(TODAY()+30),"Due in 1 Month",IF(H2<(TODAY()+60),"Due in 2 Months",IF(H2<(TODAY()+61),"Completed",IF(H2>TODAY(),"Expired")))))

    I have the following examples of returned text:

    F G H I
    Last Completed + Years Due? Status
    16-Jun-15 1 16-Jun-16 Due in 1 Month
    16-Jul-15 1 16-Jul-16 Due in 1 Month
    16-Aug-15 1 16-Aug-16 Due in 1 Month
    16-Sep-15 1 16-Sep-16 Due in 1 Month
    16-Mar-16 1 16-Mar-17 Expired

    Please please can you advise and thank you in advance for any help.

    Wintye
    Last edited by wintye; 08-24-2016 at 09:21 AM. Reason: Solved

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Function within and function x4 - Display text when date is less than 30 from today et

    I think you may have some of your logic reversed.

    Try

    =IF(F2="","",IF(H2<=TODAY(),"Expired",IF(H2<(TODAY()+30),"Due in 1 Month",IF(H2<(TODAY()+60),"Due in 2 Months","Completed"))))

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Function within and function x4 - Display text when date is less than 30 from today et

    You can also use a lookup for this, instead of the nested ifs..

    Something like

    =IF(F2="","",IFERROR(LOOKUP(H2-TODAY(),{1,31,61},{"Due in 1 Month","Due in 2 Months","Completed"}),"Expired"))

  4. #4
    Registered User
    Join Date
    08-24-2016
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    2

    Re: Function within and function x4 - Display text when date is less than 30 from today et

    I cannot thank you enough, honestly, you've made my day :-) THANK YOU

    I've had no excel training and have just played around and endless Google searches to get as far as I have so I'm not surprised my logic was the wrong way round :-). There was just too much to get my head round so I didn't even think about the dates lol.

    Again thank you so much

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Function within and function x4 - Display text when date is less than 30 from today et

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 10
    Last Post: 09-06-2023, 08:19 PM
  2. [SOLVED] Stop Today Date Function From Updating When Coping Text
    By stoicy in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-24-2016, 05:25 PM
  3. [SOLVED] How do you display the first day of a given month using the today() function
    By john dalton in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-19-2013, 07:42 AM
  4. TODAY() function to text
    By reggie1000 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-02-2009, 10:30 AM
  5. Using text and TODAY function
    By Skoal in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-22-2008, 09:39 PM
  6. =TODAY() function to fix the date
    By starguy in forum Excel General
    Replies: 4
    Last Post: 07-14-2006, 09:18 AM
  7. Today() or Date() function help
    By julisimo in forum Excel General
    Replies: 8
    Last Post: 01-03-2005, 01:23 PM

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