Skip to main content

Include Saturdays in Weekday count?

  • July 10, 2018
  • 2 replies
  • 43 views
A
Forum|alt.badge.img+19

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?

    This topic has been closed for replies.

    2 replies

    T
    • Tyler_Kurlas
    • Participating Frequently
    • July 10, 2018

    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!

    A
    Forum|alt.badge.img+19
    • AlliAlosa
    • Author
    • Inspiring
    • July 10, 2018

    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!


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

      Terms & ConditionsAccessibility statement