0

I've been using a calculated column with some IF statements to calculate the number of business days between 2 dates.

=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))

This returns the number of business days perfectly, assuming there are 2 dates entered.

The 2nd date wont always be entered in my system, so is there a way to use ISBLANK or another method to default the 2nd date to Today's date? This would allow me to calculate the number of business days between 2 dates or if the date is not defined yet, then calculate the business days up to the current date.

I've exhausted by search foo and can't find a way to do this via a calculated column. I'm using Nintex workflow/Forms so i'm able to achieve the same effect in a much more unglamorous way but curious if a calculated column could help me out!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.