Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Formula IF Due date help!

Options
Katie Carroll
edited 12/09/19 in Archived 2017 Posts

Hi,

 

I'm trying to do the below:

 

- When a date range - TODAY () is less than 30 it shows 'Current,'

- When a date range - TODAY () is less than 60 it shows '30'

- When a date range - TODAY () is less than 90 it shows '60'

- When a date range - TODAY () is less than 90 it shows '90'

 

I can get it to say current, and it works well, but I can't get any of the others, and if I try to insert an AND function it comes up as invalid.

 

Formula:

 

=IF([Due Date]3 - TODAY() <= 30, "CURRENT", IF([Due Date]3 - TODAY() <= 60, "30", IF([Due Date]3 <= 90, "60", IF([Due Date]3 - TODAY() <= 90 - 120, "90"))))

Tags:

Comments

  • Rob Hagan
    Rob Hagan ✭✭✭
    Options

    Hi Katie,

     

    I'm assuming that you are doing an "Aged Debtors" kind of computation.

     

    In which case, this formula does give that behaviour:

    =IF((TODAY() - [Due Date]1) <= 30, "CURRENT", IF((TODAY() - [Due Date]1) <= 60, "30", IF((TODAY() - [Due Date]1) <= 90, "60", IF((TODAY() - [Due Date]1) <= 120, "90", "120"))))

     

    This classifies the transactions into buckets each of which is 30 days in elapsed duration (not business days), with today and 30 days before today being classified as CURRENT.

     

    Alternatively, if you want the more classic bookkeeping notion of "30, 60, 90 days in arrears" based on a monthly accounting cycle, then I suggest that you grab the "year*12 + month" of the due date and the "year*12 + month" of today and use that in place of the due date and today in the computation. That is, drop the day part of each date and work in whole months only, like YEAR([Due Date]1) * 12 + MONTH([Due Date]1).

     

    I hope that this helps.

     

    Cheers,

     

    Rob.

  • Katie Carroll
    Options

    Hi Rob,

     

    Thanks for your prompt reply!

     

    Unfortunately the fromula - which does make sense for me, is producing CURRENT as a result regardless of what date is in there, for example I set a date of November, and all it gave me was CURRENT. 

     

    Any ideas?

     

    Thanks :)

  • Rob Hagan
    Rob Hagan ✭✭✭
    edited 03/22/17
    Options

    Hi Katie,

    Can you confirm that the [Due Date] field is of type Date and that the field in which you have placed the formula is of type Text/Number?

    Also, that you have put a whole lot of dates in your [Due Date] column and then copied down the formula (from row 1) beside each of these (in the [Status] column below).

    The following is what I had in my "play sheet" that I used to verify the operation of the formula before posting it to you (using Australian date format) - and for some reason that I don't understand my copying into this post has resulted in a dot between the fields...

    Due Date . Status

    01/01/16 . 120

    31/01/16 . 120

    01/03/16 . 120

    31/03/16 . 120

    30/04/16 . 120

    30/05/16 . 120

    29/06/16 . 120

    29/07/16 . 120

    28/08/16 . 120

    27/09/16 . 120

    27/10/16 . 120

    26/11/16 . 90

    26/12/16 . 60

    25/01/17 . 30

    24/02/17 . CURRENT

    26/03/17 . CURRENT

     

    Cheers,

    Rob.

This discussion has been closed.