Forum Discussion
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 and it worked perfect, but I do not know how to create a formula that only counts the business days. Can anyone help me with this?
=TODAY()-Created
2 Replies
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)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.
