User Report to See Vacations This Year, But also adds business holidays dates from User's Schedule?

[Kundanism]

HI

Looking for your expertise in regard to pull only vacation days without Saturday & Sunday and also exclude exception holidays from Schedule.

Actually, created one User based report, and added two text mode columns,

1) Column name: Vacation this year, but this does not able exclude the exception days registered from user schedule, for example 31st December. This day is holiday at our office which is registered in user schedule

Text code "Vacation this year":

listdelimiter=<br>

listmethod=nested(reservedTimes).lists

name=Vacation This Year (dd/mm/yy)

textmode=true

type=iterate

valueexpression=IF({endDate}>$$TODAYey,"",IF({endDate}>$$TODAYby,CONCAT({startDate}," - ",{endDate}),""))

valueformat=HTML

2) Column 2: Vacation in number days total in number: This collects total number of days for each time vacation taken by user during this year.

Text code "Total vacation days":

displayname=Vacation Days in 2020 (Working Days Only)

listdelimiter=<br>

listmethod=nested(reservedTimes).lists

textmode=true

type=iterate

valueexpression=IF({endDate}>$$TODAYey,"",IF({endDate}>$$TODAYby,SUM(ROUND(WEEKDAYDIFF({startDate},{endDate})),0),""))

valueformat=HTML

Looking your guidance.

Please.

Mvh

Kundan.

[ChloeRo]

I don't think there's anything wrong with the formulas themselves. I'm seeing the same results in my environment. I think it has more to do with the timezones in Workfront.

I did a similar report and added columns for "Start" and "End" dates, setting valueformat to display longAtDate (here's the code if you want to test this for yourself):

displayname=Start

listdelimiter=div

listmethod=nested(reservedTimes).lists

textmode=true

type=iterate

valueexpression=IF({endDate}>$$TODAYey,"",IF({endDate}>$$TODAYby,{startDate},""))

valueformat=longAtDate

-------------------

displayname=End

listdelimiter=

listmethod=nested(reservedTimes).lists

textmode=true

type=iterate

valueexpression=IF({endDate}>$$TODAYey,"",IF({endDate}>$$TODAYby,{endDate},""))

valueformat=longAtDate

-------------------

Here's what I noticed:

• The "time off" timestamp adjusts, based on the user's timezone relative to mine. When the user is in the same timezone as me, the date shows 12am, so that day gets counted. When the user is in a DIFFERENT timezone than me, it displays a time well past the end of our "workday" - either 9pm or 10pm. If I had a user in MST, I would've also seen results with 11pm. This "adjustment" prevents the day from being counted since Workfront reads that and determines the "time off" start time occurs after the workday has ended.

• Collection results cannot be aggregated, so you can remove the "SUM()" portion of your formula. You will only have 1 set of start and end dates for each row of the "reservedTimes", so there will never be anything to add up.
• I removed "ROUND()" from the formula as well and it still returned whole numbers. DATEDIFF formula will return decimals, but apparently WEEKDAYDIFF will not. So you can get rid of the "ROUND()" function in your expressions.

Here's what doesn't make sense to me: PST to EST is +3h. So I'd expect the timestamp of EST users to show "3:00am", not "9:00pm". But, based on what I'm seeing, it seems like Workfront is retaining the date, regardless of the timezone, then taking 12:00am PST and SUBTRACTING the time difference (so instead of adding 3 hours, it's removing 3 hours, but the date remains the same).

I have no idea if my theory is correct or if it's considered "working as intended" but it's probably worth asking Support to see if they can help you find a workaround (or at least explain how the math is working).

[Kundanism]

Thanks a lot @Chloe Rock‚ .

I am on vacation until 3rd Jan, I will check and update you with feedback.

Thanks a lot for your great help.

Have a nice evening and a prosperous new year ahead!

Mvh

Kundan.

[Kundanism]

It works for start and end date, thanks a lot @Chloe Rock‚ . Is it possible to change the color of a column and the text (color / bold) in text mode?

Mvh

Kundan.