Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculate workdays between two dates, but have multiple dates columns

I'm tearing my hair out with this one and would appreciate the help!

 

I have tried various options with the CALENDAR and CALENDARAUTO function as well as trying to create the column within my main table and can't seem to make it work, all solutions searched seem to have the assumption there is only two date columns to work with so think this is my issue. 

Basically, I have multiple columns e.g.

 

StartAssessPrepSubmitIssueAfter
1 January 20221 January 20222 January 202215 January 202220 January 202223 January 2022
12 September 202214 September 202219 September 202231 September 20221 October 20224 october 2022

 

I did a date diff calc to work out the days between each of these, but obviously, this would include weekends.

StartAssesswdays between St+PPrepwdays between As+SSubmitwdays between P+SIssuewdays between S+IAfterwdays between I+Af
31 December 20213 January 202214 January 2022115 January 2022820 January 2022424 January 20221
12 September 202214 September 2022319 September 2022430 September 2022101 October 202214 october 20222


I want to exclude weekends and have the count only show weekdays (not fussed about holidays at the current moment, and can probably work that one out once I have done this bit). 


I tried the following:

 

  • Creating a calendar and referencing back to it
    = (ADDCOLUMNS( CALENDARAUTO(), "Formatted Date", FORMAT([Date],"dd MMMM yyyy"))

    WEEKDAY = WEEKDAY(DetermineDay[Formatted Date])

    WORKDAY = IF(OR(DetermineDay[WeekDay]=1,DetermineDay[WeekDay]=7),0    )

    But then I got an error when trying to reference back to this table for the date differences
 
  • Also this one

    Workdays =
    COUNTROWS (
    FILTER (
    ADDCOLUMNS (
    CALENDAR ('Sheet1'[Start], 'Sheet1'[Assess]),
    "Day of Week", WEEKDAY ( [Date], 2 )
    ),
    [Day of Week] <> 6
    && [Day of Week] <> 7
    ))

    But I'm getting start and end date cannot be blank error

  • I also tried following  https://www.youtube.com/watch?v=zzSoA8RuJR8 but it doesn't seem to like working for me either  

 

I'm really stuck, would appreciate any help here! 

Thank you!

Need Help

3 Replies