Forum Discussion

Mariel016's avatar
Mariel016
Copper Contributor
Apr 18, 2025

How to calculate the number of business days between created date column and todays system date?

I am trying to calculate the count of business days between the SharePoint system created column and Today system date but just for business days. Initially, I created the calculated column Days Open...
Lists
SharePoint Online
Rob_Elliott's avatar
Rob_Elliott
Silver Contributor
Apr 20, 2025

Mariel016the formula is shown below and assumes that the day the item was created is the first working  day. If you don't want the first day counted you'll need to remove the +1. As you'll see in the image, those items created yesterday on 19 April (a Saturday) result in 0 working days.

=IF(ISERROR(DATEDIF(Created,Today(),"d")),"",(DATEDIF(Created,Today(),"d"))+1-INT(DATEDIF(Created,Today(),"d")/7)*2-IF((WEEKDAY(Today())-WEEKDAY(Created))<0,2,0)-IF(OR(AND(WEEKDAY(Today())=7,WEEKDAY(Created)=7),AND(WEEKDAY(Today())=1,WEEKDAY(Created)=1)),1,0)-IF(AND(WEEKDAY(Created)=1,(WEEKDAY(Today())-WEEKDAY(Created))>0),1,0)-IF(AND(NOT(WEEKDAY(Created)=7),WEEKDAY(Today())=7),1,0))

 

But be aware that the value in calculated column is actually static and does not update dynamically. It's only updated when the item is edited. For dynamic updates you need to use JSON column formatting.


Rob
Los Gallardos
Microsoft Power Automate Community Super User
Global Power Platform Lead, WSP (and classic 1967 Morris Traveller driver)

 

  • Mariel016's avatar
    Mariel016
    Copper Contributor
    Apr 21, 2025

    Hi Rob,

     

    Good morning and thank you for your time and knowledge, but I feel I wasted your time as the field will need to be dynamic. This is for a RAID log and establishing business days open is something that needs to update without having to do any updates to the line item.  

Resources