Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Modify Formula (2003)

    Hi - I have the formula =IF(H6<>"","Complete",IF(DAYS360(G6,TODAY())>0,DAY S360(G6,TODAY())&" days overdue","Action Underway")) with calculates the days past a date.
    It works well except when there is one day overdue, where it returns 1 Days overdue.

    Any suggestions has to what the formula would look like so that it would read "1 Day Overdue" followed by days plural for subsequent dates - ie 4 Days overdue

    Also in cell F6 there is a $ value($123) - I've tried &F6&"is" in the above formula but keep getting errors or the text appears in the wrong place - I want to try and achieve a value that would read "$123 is 1 day overdue" or "$123 is 2 days overdue"

    Thanks for any suggestions

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Formula (2003)

    This one should do the trick.

    =TEXT(F6,"$#,##0.00") & " is " & IF(H6<>"","Complete",IF(DAYS360(G6,TODAY())>0,DAYS 360(G6,TODAY())&IF(DAYS360(G6,TODAY())=1," day overdue"," days overdue"),"Action Underway"))

  3. #3
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Formula (2003)

    Thanks very much for the reply - will give it a go

    Regards

  4. #4
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Formula (2003)

    Mbarron

    I tied your formula - works fine except that it also put the TEXT value in front of "Action Underway" so I get "$123 is Action Underway". This is part of the problem I was having where the TEXT follows the wrong part.

    The values should be either - "Complete", "$123 is 10 days overdue", or "Action Complete"

    Any thoughts?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Modify Formula (2003)

    =IF(H6<>"","Complete", IF(DAYS360(G6,TODAY())>0,TEXT(F6,"$#,##0.00") & " is " &DAYS360(G6,TODAY())&IF(DAYS360(G6,TODAY())=1," day overdue"," days overdue"),"Action Underway"))

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify Formula (2003)

    Thanks Steve - thats it

    Regards

Posting Permissions

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