PTO Duration Text Mode

Hey Monique, We have a help article that goes into how to set up a new report to display time off.. check it out: https://experience.workfront.com/s/article/View-User-Personal-Time-Off-1613229551

However, that doesn't give you the option of Duration. Here's some basic text mode that will append " days", round to the nearest hundredth, and provide a result. :)

displayname=Duration

linkedname=direct

textmode=true

valueexpression=CONCAT(ROUND(DATEDIFF({endDate},{startDate}),2)," days")

valueformat=HTML

I hope this helps! Thanks, Dustin Martin Assigned Support Engineer Workfront

THANK YOU SO MUCH DUSTIN!!!! I went to that article and dug around then went to the API explorer but didn't really find what I wanted. Attempted to make a column with what I know of text mode but for some reason wasn't giving any results. I could not figure out that expression. I wish I could get a sum on the summary tab but I know that's not possible since it's a calculated column. One last question, I changed the calculation so it would only include workdays and not weekends but my columns are returning one day less than I want it to. For example, if you take one day off, the start and end are the same date so the difference is 0. How do I get that to become a 1? Also looking at the calculations the white is correct and the blue is off.

displayname=Number of Days

linkedname=direct

shortview=true

textmode=true

valueexpression=CONCAT(WEEKDAYDIFF({startDate},{endDate})," days")

valueformat=HTML </pre>

Monique Evans Stanley Black & Decker, Inc.

Hey Monique, You're welcome! We're off on the right track. Give this code a try:

aggregator.function=SUM

aggregator.name

=Duration

aggregator.valueexpression=CONCAT(ROUND(WEEKDAYDIFF({startDate},{endDate}),2)," days")

aggregator.valueformat=html

displayname=Duration Summed

linkedname=direct

textmode=true

valueexpression=CONCAT(ROUND(WEEKDAYDIFF({startDate},{endDate}),2)," days")

valueformat=HTML

It should end up looking like this:

The issue is going to center around the PTO that isn't a full day off. If you'd rather, you could do Hours instead of Days using WORKMINUTESDIFF(date1, date2) as seen here:

https://experience.workfront.com/s/article/Calculated-data-expressions-679086747 You would then just add /60 to show hours instead of minutes. The down side comes when time spans multiple days. Our consulting team would be better able to handle this more efficiently with you, but I hope this gets you started down the right path. :) (I was thinking they could build an IF(workminutesdiff>480,CONCAT(" hours"),CONCAT(" days")) ) I don't know if that'll work, it was more a pseudo-code to give you an idea of what should theoretically be possible.. but I'm only about a 6/10 with reporting.. whereas our consulting team... they're the gurus there.. Good luck! Dustin Martin Assigned Support Engineer Workfront

Just kidding, figured it out using your genius Workday Minutes suggestion! If you're going to Leap, I'm totally hugging you!! If anyone is interested in the code, here you go. This will give you the time off in days summed no matter if someone is doing one day, partial day, or a few days spanning a weekend! My users are going to be so happy :)

valueexpression=CONCAT((ROUND(WORKMINUTESDIFF({endDate},{startDate}),2))/480," days")

linkedname=direct

valueformat=HTML

aggregator.valueexpression=CONCAT((ROUND(WORKMINUTESDIFF({endDate},{startDate}),2))/480," days")

aggregator.function=SUM

aggregator.valueformat=html

aggregator.name

=Duration

displayname=Work Minutes (In Days) Duration Summed

textmode=true

You can see my progress of code and columns as I finally got to the desired outcome of the last column!

Monique Evans Stanley Black & Decker, Inc.