Forum Discussion
Need Help fixing datediff. Seeing 'same day' and 'BLANK' data(for one of the day) as same results.
I'm creating a report for Trouble Tickets. I have 2 columns representing 'Date Created' and 'Date Completed'. The resulting Column('# of Days to Clear 2') from _datediff generates my results but I'm getting tickets closed on the same day and Tickets that do not have a value under 'Date completed' as 'remaining open'. How do I get same-day closed tickets to be a part of the 'Cleared in 7 days or less' .
see below:
Delguy87 Instead of DATEDIFF, use:
( [Date Completed] - [Date Created] ) * 1.Then check for >= 0 && <= 7Those without a Date Completed will be negativeThanks for the reply. you helped me to a solution. this was what ended up working for me. For some reason, i was getting an error for ' >= 0 && <= 7'
# of Days to Clear 2 = var _diff = ([Date Completed]-[Date Created]*1)returnSwitch(True() ,_diff <=-1, "Remaining Open",_diff <=7, "Cleared in 7 days or less",_diff <=30 , " Cleared in 8 to 30 days",_diff <=60 , " Cleared in 31 to 60 days",_diff <=90, " Cleared in 61 to 90 days",_diff <=120, " Cleared in 91 to 120 days",_diff >120, "Cleared in 120 days or more"--_diff =blank(), "remaining open")
4 Replies
Delguy87 Instead of DATEDIFF, use:
( [Date Completed] - [Date Created] ) * 1.Then check for >= 0 && <= 7Those without a Date Completed will be negativeThanks for the reply. you helped me to a solution. this was what ended up working for me. For some reason, i was getting an error for ' >= 0 && <= 7'
# of Days to Clear 2 = var _diff = ([Date Completed]-[Date Created]*1)returnSwitch(True() ,_diff <=-1, "Remaining Open",_diff <=7, "Cleared in 7 days or less",_diff <=30 , " Cleared in 8 to 30 days",_diff <=60 , " Cleared in 31 to 60 days",_diff <=90, " Cleared in 61 to 90 days",_diff <=120, " Cleared in 91 to 120 days",_diff >120, "Cleared in 120 days or more"--_diff =blank(), "remaining open")Delguy87 That was short-hand. The actual code would have been _diff >= 0 && _diff <= 7
thanks, that worked as well!