Results 1 to 7 of 7

Thread: Can Someone please help with a formula

  1. #1

    Can Someone please help with a formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello.
    G2 = TODAY()
    F17=Date completed

    Formula Needed - Does the Year in the F17 Fall within (=) to this year, if so - Look at the date in F17, is it less than 91 days past that date or >181? If it is not (=) to this year Look at G2, is it less than 91 days past that date or >181?

    Thank You!!!

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by steved22 View Post
    Hello.
    if so - Look at the date in F17, is it less than 91 days past that date or >181? If it is not (=) to this year Look at G2, is it less than 91 days past that date or >181?

    Thank You!!!
    Past which date????

  3. #3
    Hercules.... Thank you for responding. This is driving me crazy.

    I have a quarterly preventative maintenance that needs to be done for the fire dept. I have 4 rows representing the quarter Q-1 through Q-4. G2 just has today's date for reference.
    I am entering a date completed in cells like this:

    A-1 through G1 is all descriptions. F1 I am entering the date the last PM was completed. Below. I Have written formulas like =IF((AND(ISBLANK(F17),($G$2-DATE(YEAR($G$2),1,0)<= 90))), "NO","Overdue")
    When I open the spreadsheet. I would like to know that if I am over the last day in the 1st quarter that my PM is overdue for that quarter. My problem is referencing the date in the date completed cell. If the date from last year is in there and todays date is not past the last day in the quarter, than it should Not be Overdue. If I completed the PM before the quarter ends and the date is in 2017, then it should still Not be Overdue. I hope you can help. Thank You in advance. This is only one of the formulas I wrote above. There were 50 others. I cant get all the conditions straight. If its even possible.

    A1 F1 G1
    Desc Date Completed Past Due?
    Q-1 2/3/16 NO or Overdue
    Q-2 5/3/16 NO or Overdue
    Q-3 9/6/16 NO or Overdue
    Q-4 11/21/16 NO or Overdue

    Click image for larger version. 

Name:	Capture.JPG 
Views:	2 
Size:	51.6 KB 
ID:	6356

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    The problem with your formula is that its not detailed enough to deal with all the possibilities.
    All its doing is saying if there is nothing in F17 AND todays date is < = 90 days into the year, THEN return "NO", otherwise return "Overdue". This means that if there is ANY date in F17 it will return "Overdue" because the AND() function returns FALSE.
    If you can list all the conditions that need to return "Overdue" we can construct a nested if formula to handle it. I need to go but will follow up tomorrow

  5. #5
    Hercules..... You still there?

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Hello Steved22
    Sorry I wasn't there when you posted last. Ive taken another look and I still find the different wording a bit contradictory Im afraid.. It could be me, I suppose This does mean that Im not sure if Im on the right track.
    Anyway Ive put an example together, and we might progress more if you tell me if its anywhere near what yyou need, and it does show how you can use multiple (nested) IF() functions to make the formula handle more possibilities in the data. Its 17:30:00 here now and I will be back on line in about 3 hours.
    Attached Files Attached Files
    Last edited by Hercules1946; 2017-02-01 at 06:37 PM.

  7. #7
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by Hercules1946 View Post
    Hello Steved22

    Anyway Ive put an example together
    Was it any help?

Posting Permissions

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