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!
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"))))
Comments
-
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.
-
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
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives