How to compare dates in a report to mark tasks Early, Late, or On Time. | Community
Skip to main content
S
ShawnMe
Level 3
July 24, 2020
Solved

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

  • July 24, 2020
  • 3 replies
  • 2405 views

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.

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by skyehansen

    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.

    3 replies

    A
    AdinaPi1
    Level 9
    July 27, 2020

    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.

    S
    ShawnMeAuthor
    Level 3
    July 27, 2020

    It is a date field without the time being displayed.

    N
    NRYNR
    Level 10
    July 28, 2020

    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

      S
      ShawnMeAuthor
      Level 3
      July 29, 2020

      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

      N
      NRYNR
      Level 10
      July 29, 2020

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

      --Narayan

      skyehansen
      Community Advisor and Adobe Champion
      skyehansenCommunity Advisor and Adobe ChampionAccepted solution
      August 1, 2020

      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.

      S
      ShawnMeAuthor
      Level 3
      August 3, 2020

      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.

      S
      ShawnMeAuthor
      Level 3
      August 3, 2020

      Thanks again, Skye. It was driving me batty.

      Terms & ConditionsAccessibility statement

      Loading footer...