Jul 10, 2018 06:12 AM
I am attempting to set-up a database to track employee’s Paid Time Off (PTO). Ultimately I need to convert the days off to hours but I first need to solve this problem…
I have set-up a formula field that counts the amount of days off using the WEEKDAY_DIFF() function, but I need to include Saturdays in that calculation as well.
Is there a way to scan the dates {Start Date} and {End Date}, and return how many Saturdays are included in the date range?
Jul 10, 2018 10:54 AM
Hi Neads,
Here’s the steps so you can see it broken down, and a consolidated formula where the temporary variables have been removed.
{start day} = WEEKDAY({Start Date})
{days apart} = DATETIME_DIFF({End Date},{Start Date},‘days’)
{days until sat} = 6-{start day}
{# sats} = ROUNDDOWN(1+({days apart}-{days until sat})/7,0)
{consolidated} = ROUNDDOWN((1+(DATETIME_DIFF({End Date},{Start Date},‘days’)-(6-WEEKDAY({Start Date})))/7),0)
Good luck!
Jul 10, 2018 12:43 PM
You, good sir, are brilliant! Can’t say I ever would have gotten there on my own. Thanks so much for the help.