Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
Is it possible to count how many due date already passed or encountered? Please find sample table and desired output (Count of Due Date column) below:
Due Date | Current Date | Count of Due Date |
---|---|---|
1/1/2014 | 12/31/2013 | 0 |
1/1/2014 | 1/1/2014 | 1 |
1/1/2014 | 1/31/2014 | 1 |
1/1/2014 | 2/1/2014 | 2 |
1/1/2014 | 2/25/2014 | 2 |
1/1/2014 | 3/1/2014 | 3 |
1/5/2014 | 1/4/2014 | 0 |
1/5/2014 | 1/5/2014 | 1 |
1/5/2014 | 2/3/2014 | 1 |
1/5/2014 | 2/5/2014 | 2 |
1/5/2014 | 3/1/2014 | 2 |
1/5/2014 | 3/5/2014 | 3 |
1/5/2014 | 3/6/2014 | 3 |
1/5/2014 | 3/15/2014 | 3 |
Above shown that there are two due dates (Jan 1, 2014 and Jan 5, 2014). Also, find below the conditions:
If Current Day < Due Date, then it's 0.
If Current Day > Due Date, then it's 1 or 2 or 3 or 4
It's so tricky because you have to compare the date, month, and year as well. I don't know yet how to solve this one.
If there's quickest way or simpler way to solve this, please don't hesitate to extend help. Thank you in advance!
Best Regards,
Bill
Hi Bill
Can you specific condition below? When it become 1? When it become 2?...
If Current Day > Due Date, then it's 1 or 2 or 3 or 4
Regards,
Sokkorn
What I meant is this,
Say for example the due date is 1/5/2014, and today is 2/20/2014
This means that, we already have 2 due dates past, 1/5/2014 and 2/5/2014
That's why I'm getting hard on that scenario. But I'm looking for possible solutions.
Hi Bill,
Maybe this expression can do this job
If(CurrentDate<DueDate,0,
((Year(CurrentDate)*12)+Month(CurrentDate)) - (((Year(DueDate)*12)+Month(DueDate)))+1
)
Regards,
Sokkorn