How to compare dates in a report to mark tasks Early, Late, or On Time.

[ShawnMe]

I am trying to compare a custom data field (Requested Delivery Date) to the Actual Completion Date to be able to label tasks Early, Late, or On Time in a report column.

Among the expressions/text blocks I have tried are the following:

displayname=Timing1

textmode=true

valueexpression=IF({actualCompletionDate}={DE:Requested Delivery Date},"On Time",IF({DE:Requested Delivery Date}>{actualCompletionDate},"Early",IF({actualCompletionDate}>{DE:Requested Delivery Date},"Late","")))

valueformat=HTML

displayname=Timing2

textmode=true

valueexpression=IF(WEEKDAYDIFF({DE:Requested Delivery Date},{actualCompletionDate})=0,"On Time",IF({DE:Requested Delivery Date}>{actualCompletionDate},"Early",IF({actualCompletionDate}>{DE:Requested Delivery Date},"Late","")))

valueformat=HTML

Neither worked properly. I could not get On Time to work at all in the first example (I assume this is because of time stamping?), so I tried the second one. And while using WEEKDAYDIFF for the On Time designation worked, both versions had some wrong calculations (eg. Requested 8/6/20 and Actual 7/24/20 calculated to Late instead of early).

I am sure there must be an easy way to do this that I am just missing. Any help would be appreciated.

[AdinaPi1]

Is your custom field in Date/Time format, or just Date? We had the same problem, and had to switch our custom date fields to Date /Time. I haven't tested your formulas yet, but wanted to check on that first.

[ShawnMe]

It is a date field without the time being displayed.

[NRYNR]

Hey Shawn,

This is one of those topics I'd like to reply to but it'll take forever in text to get anywhere. Since a discussion would likely yield some results, I invite you to join the WFPro Decompression Session this Friday for a quick chat about the calculation and approach. There isn't much you can throw at us that we haven't seen.

Thanks,

Narayan

[ShawnMe]

Hi Narayan,

I am a little familiar with the WFPro site, but not the sessions. What would I have to do to attend?

Thanks,

Shawn

[NRYNR]

Just follow the link posted in WFPro Live Upcoming when the time comes. Talk to you then.

--Narayan

[ShawnMe]

Thank you. Is the time Noon-2pm PST? If so, I will only be able to make the first hour.

[NRYNR]

Confirmed.

[Kundanism]

Hei Narayan, @NRYN R - inactive‚

Thats a great help, could you please tell the session time (European). Which time zone it is at the moment?

Mvh

Kundan.

[NRYNR]

Hi Kundan, There’s only one<> at the moment. 3pm Eastern this Friday. Thanks, Narayan

[skyehansen · Accepted solution]

Shawn, just wanted to check in with you that you were able to get Doug's suggestion to use CLEARTIME up and running for your calculation. I got it working on my side with a much smaller example (i.e. valueexpression=CLEARTIME({plannedStartDate})) -- however, if you still find you can't make your original example work, let the Community know and someone will help.

On a related note, I now have a ticket in with the helpdesk asking why I'm getting a different answer using a calculated field. But as long as you stick with valueexpression in a view column, I feel sure it should work.

[ShawnMe]

Hi Skye,

Sorry, I had to let this go for the weekend and try to go at it with fresh eyes today. I was able to make the CLEARTIME syntax work on the calculation of the number of days between the Requested Due Date and the Actual Completion Date, but the calculation is still off in some circumstances. It makes me wonder if I am experiencing the same issue that you are.

What I wasn't able to make work was adding CLEARTIME into the Early/Late/OnTime expression. I am going to give both another shot today.

The thing that complicates the expressions is that this is a transitional period during which not all of the Requested Due Date fields are filled out. So I have to use a nested IF statement to determine which field to compare the Actual Completion Date with. I kept that out of the original post to simplify things, because the nested IF is already working.

[skyehansen]

Could very well be. Can you share your current valueexpression? Just want to make sure you're only using CLEARTIME for the actual completion date, as I don't think it needs to be used for the requested due date if this doesn't have a time component.

Otherwise, I am finding that using cleartime in a calculated field somehow results in the date being pushed out by one day, so that's the ticket I submitted to the helpdesk.

[ShawnMe]

I was actually able to get it to work by mostly starting from scratch and simplifying the nested IF that determines the field to compare with the Actual Completion Date. But now that you mention only using CLEARTIME on the Actual Completion Date, I think that was the issue. Here is what works.

valueexpression=IF(WEEKDAYDIFF(CLEARTIME({actualCompletionDate}),IF(ISBLANK({DE:Requested Delivery Date}),{DE:Meeting / Due Date},{DE:Requested Delivery Date}))=0,"On Time",IF(WEEKDAYDIFF(CLEARTIME({actualCompletionDate}),IF(ISBLANK({DE:Requested Delivery Date}),{DE:Meeting / Due Date},{DE:Requested Delivery Date}))<0,"Late","Early"))

I just tested your finding that CLEARTIME pushes the number of days out by one, and it is true, albeit inconsistent. I was using it on each date field in the valueexpression counting the number of days between the Actual Completion Date and a date field determined by an IF statement, and some (not all) results were off by a day. Here is that valueexpression.

valueexpression=WEEKDAYDIFF(CLEARTIME({actualCompletionDate}),IF(ISBLANK({DE:Requested Delivery Date})&&CLEARTIME({handoffDate})>CLEARTIME({plannedStartDate}),CLEARTIME({projectedCompletionDate}),IF(ISBLANK({DE:Requested Delivery Date})&&CLEARTIME({handoffDate})<=CLEARTIME({plannedStartDate}),CLEARTIME({plannedCompletionDate}),CLEARTIME({DE:Requested Delivery Date}))))

Note that the fresh start finally made me realize that every task that does not have an entry in Requested Delivery Date SHOULD have an entry in Meeting / Due Date. I previously did not think that was the case.

[skyehansen]

fabulous work and sounds like you were able to push it over the final step. Well done: I hate nested IF statements.

I think you are seeing the exact issue I asked the helpdesk about--running a cleartime on a calc date field will push it over to the following day on the valueexpression.

[ShawnMe]

Thanks again, Skye. It was driving me batty.