Forum Discussion

Darrio's avatar
Darrio
Copper Contributor
Mar 16, 2023

Calculate Active Days in Recurring SharePoint Calendar

I am looking for a formula to calculate the active days of a recurring SharePoint calendar event. I created a simple formula to calculate the number of days based on the start date and end date, (=[End Time]-[Start Time]). However, this formula does not account for nonactive days in the recurrence. Example:  An event set to recur weekly (Monday-Friday) with Start Date 1/2/223 and End Date 1/22/2023. The formula returns a value of 19. This includes the total number of days between the two dates including weekends. The true value is 15 when only counting Monday-Friday the two dates. Is there a way to calculate only active days in the recurring event (i.e. Monday-Friday)?

 

2016
Lists
SharePoint Online

2 Replies

  • Supernova's avatar
    Supernova
    Copper Contributor
    Mar 16, 2023
    Hi Darrio,

    To calculate only the active days in a recurring SharePoint calendar event, you can use the following formula:

    =(DATEDIF([Start Time], [End Time],"d")+1)-((DATEDIF([Start Time], [End Time],"d")+1)/7*2)-IF(WEEKDAY([End Time],1)=7,1,0)-IF(WEEKDAY([Start Time],1)=1,1,0)

    This formula takes into account the non-active days in the recurrence, such as weekends, and returns the total number of active days between the start and end dates. Here's how the formula works:

    DATEDIF([Start Time], [End Time],"d")+1: This calculates the total number of days between the start and end dates, including weekends.
    (DATEDIF([Start Time], [End Time],"d")+1)/7*2: This calculates the number of weekends in the date range and subtracts them from the total number of days.
    IF(WEEKDAY([End Time],1)=7,1,0)-IF(WEEKDAY([Start Time],1)=1,1,0): This accounts for any partial weekends at the start or end of the date range, subtracting them from the total number of days.
    By subtracting the non-active days from the total days, the formula returns the number of active days in the recurring event.

    Note that this formula assumes that the recurring event is set to occur weekly (Monday-Friday) as in your example. If the recurrence pattern is different, you may need to modify the formula accordingly.

    I hope this helps! Let me know if you have any questions.
  • RobElliott's avatar
    RobElliott
    Silver Contributor
    Mar 16, 2023

    Darrio to calculate the number of working days (Monday-Friday) between 2 dates which in my list are Start and End, add a calculated column and use the following formula:

    =(DATEDIF(Start,End,"D"))-INT(DATEDIF(Start,End,"D")/7)*2-IF(WEEKDAY(End)<WEEKDAY(Start),2,IF(OR(WEEKDAY(End)=7,WEEKDAY(Start)=1),1,0))

     

    Rob
    Los Gallardos
    Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

Resources