Include Saturdays in Weekday count?


#1

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?


#2

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!


#3

You, good sir, are brilliant! Can’t say I ever would have gotten there on my own. Thanks so much for the help.