Formula IF Due date help!

Katie Carroll

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"))))

Rob Hagan

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

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

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.