PTO Duration Text Mode

[MoniqueEvans]

I'm in the works of getting my users to start using Workfront to log their time off to help with resource planning. I know they are going to ask WIIFM (What's In It For Me?) and my answer is a quick report that pulls all their time into one place and (hopefully) adds up the number of days used. Right now everyone is doing this in their own method. I am able to get the Time Off report to pull all of the time for the user, but I cannot figure out the text mode for the duration. We need that column so that on the summary it can total the number of days used instead of the number of records. I think asking 500 people to log each day separately instead of in the actual time interval is a bit much.

[SharonFl]

Monique, Do you know if there is a way to pull all of my team's PTO onto a calendar that our group can see? I am sorry I can't help with your request though :) Sharon Sharon Flynn University of Virginia Health System

[MoniqueEvans]

Hi Sharon, Yes, you can make a time-off calendar as of the 2019.4 release. Here's the link to the product page that has a step by step video. It will explain it WAY better than I can :) Good luck! https://experience.workfront.com/s/article/2019-4-other-enhancements-742784455 Monique Evans Stanley Black & Decker, Inc.

[EXP_-_DustinMa]

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

[MoniqueEvans]

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.

[EXP_-_DustinMa]

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

[MoniqueEvans]

Thanks again Dustin! You've gotten me 90% of the way there, I'm going to put in a ticket and see if my ASE can help get us over the finish line. I'll also reach out to my consultant to see how many hours this would take if necessary! Monique Evans Stanley Black & Decker, Inc.

[MoniqueEvans]

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.

[Doug_Den_Hoed__AtAppStore]

Dustin for Mayor! Regards, Doug Doug Den Hoed - AtAppStore

[EXP_-_DustinMa]

Monique, That is absolutely awesome! I'm glad we were able to work together to get you taken care of! And yes, I'll be at Leap this year! In fact, I'm working on moving to Florida.. but that's a story for another time ;) I hope you have a fantastic Friday! All the best, Dustin Martin Assigned Support Engineer Workfront