Calculate Number of Days between two date fields and exclude weekends

[shaes2023]

We are implementing time tracking fields in multiple bases and need to calculate the total amount of time between two date fields (in days, hours & minutes) but also excluding weekends and possibly holidays. 

I've tried the following but the calculations are returning errors.

WORKDAY_DIFF(

 {To In Studio},{To Color Correction},

    'days') & " days " &

MOD(WORKDAY_DIFF({To In Studio},{To Color Correction},

        'hours'),

    24

) & " hours " &

MOD(WORKDAY_DIFF(

         {To In Studio},{To Color Correction},

        'minutes'

    ),

    60

) & " minutes"

We have the following implemented and working, but weekends are not excluded:

DATETIME_DIFF(

  {To Color Correction},{To In Studio},

    'days') & " days " &

MOD(DATETIME_DIFF({To Color Correction},{To In Studio},

        'hours'),

    24

) & " hours " &

MOD(DATETIME_DIFF(

         {To Color Correction},{To In Studio},

        'minutes'

    ),

    60

) & " minutes"

How can we set up a formula to calculate the total number of work days/hours/minutes and excluding weekends?

[Dan_Montoya]

Hi,

I would solve this by having a table that has all the calendar dates and holidays your company observes.  For example, some companies recognize Christmas Eve holidays differently depending on what day the holiday falls.  Then use a lookup field to only pull in weekdays and sum those weekdays.

If you don't care about holidays you could write an if statement that looks to see if the total is > 120 and < 168.  In which case you know that you have crossed a Saturday and Sunday.