Forum Discussion
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, (=[E...
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.
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.