Comparing 2 Date filters but clearing the time stamp

[LaurensKr1]

Hi,

I wonder if someone have run into this and potentially found a work around:

We are trying to setup a project level report to compare 2 dates with one another. For example, we want to check if Date 1 is greater than Date 2.

The issue we run into is that every date in Workfront has a timestamp attached to it. So for example, if the time on the date is after 5PM (I think this is done based on the WF instance time zone vs user time zone), Workfront will assume the date to compare falls into the next date. For example, if the actual completion date is 4/29/25 7:15PM, when comparing the date is seen as 4/30/25.

To solve this, we tried a few ways:

1. We created 2 custom fields for the 2 dates we tried to compare and within the formula we included CLEARTIME function. That did not seem to work.

2. And similar to #1, instead of the custom fields, we used the CLEARTIME function within the filter itself, also result were not expected.

A typical use case would be, I want to see if the actualCompletionDate is after the plannedCompletionDate of the task. If the planned completion date is eg.4/29/24 1PM, and the actual completion date was 4/29/24 2PM, the result is the task would be late. So time for us in this case should not be a factor we want to consider.

Is there a different way to approach this?

What factors are at play with the timestamp (eg. system time zone, project time zone, user time zone)? 

[skyehansen]

I'm not clear on what all you've tried.

I think if you're ok with making a custom field, the simplest thing would be to do all the calculation in there. So in a calculated field, if you do something like 

IF(DATEDIFF({TheLaterDate}, {TheEarlierDate}) > 1, "it's later", "it's not later")

that would work. If it really is planned completion and actual completion you're trying to compare like in your example above, then something like:

IF(DATEDIFF({actualCompletionDate}, {plannedCompletionDate}) > 1, "it's later", "not later")

would generate a result of a fraction of a day, which is less than 1, so your calculation would result in "not later". Obviously if you decide that really, you have more wiggle room in there, you can say ">2" instead, for example.

I'm not trying to steer you away from using CLEARTIME, by the way, but just want to point out that you can avoid it. Also, CLEARTIME doesn't work in a reporting filter (in case that was what you were trying to do)

[LaurensKr1]

Thank you Skye. This is helpful. Two follow up questions:

1. In the case where we want to use a filter (for example get all tasks that were completed after the original planned completion date), and if we used two calculated fields to Clear the time from the actual completion date and planned completion date resulting in two date outputs, we noticed that if the output date using cleartime is the same we still saw that if the original timestamp was eg. after 5PM an unexpected behavior. For example, after clearing the time from the planned completion date and actual completion date, we had a case where the dates appeared the same, but the original timestamp for some reason impacted the filtered result. We used the two calculated fields within the filter to compare the dates. Have you seen this before?

2. Would you advise use to steer more towards Datediff calculations?

[skyehansen]

For your first question: I'm not clear on exactly what you've tried or what your output was. I would say that if you are struggling with any concept that seems like it might need a meeting to get through, it's probably best to submit a Support ticket, since they'll have the time to walk you through what your options are.

For your second question: if you've ever heard the saying "when you have a hammer, everything looks like a nail" --  datediff is not the answer to everything (not all your problems are "nails"), but it seems to be a decent answer today.

(In particular all functions have limitations that you need to understand and the understanding of "when" to use a function is also a skill. As an example, Datediff is a very blunt instrument, in that it gives an exact difference between two dates... you will discover that some customers prefer Weekdaydiff since this gives an integer-based result that excludes weekends, but there may be other limitations with this... there's also a general complaint that there doesn't seem to be a way to exclude company-observed holidays. ANyway, just some thoughts.)

[kautuk_sahni]

@LaurensKr1 just checking in! Were you able to get this resolved? If one of the replies above helped—whether it completely solved the issue or simply pointed you in the right direction—marking it as accepted can make it much easier for others with the same question to find a solution. And if you found a different way to fix it, sharing your approach would be a great contribution to the community. Your follow-up not only helps close the loop but also ensures others benefit from your experience. Thanks so much for being part of the conversation!