How to count only weekdays between two dates

The content of this article has been updated and migrated to a Looker Help Center article.

But, the information in the comments is useful, and so the article will remain here for discussion. For the actual contents, please visit the Help Center.

4 6 4,724
6 REPLIES 6

Levi_Davis
Participant I

For each old LookML code block in this article and its comments, we just added the New LookML equivalent code.

Adapted for Presto:

###Presto

dimension: weekdays_between {
type: number
sql: DATE_DIFF('day',${start_raw},${stop_raw}) - ((FLOOR(DATE_DIFF('day',${start_raw},${stop_raw})/7)*2) +
CASE WHEN DAY_OF_WEEK(${start_raw}) -
DAY_OF_WEEK(${stop_raw}) IN (1,2,3,4,5) AND DAY_OF_WEEK(${stop_raw}) !=0
THEN 2 ELSE 0 END +
CASE WHEN DAY_OF_WEEK(${start_raw}) != 0 AND DAY_OF_WEEK(${stop_raw}) = 0
THEN 1 ELSE 0 END +
CASE WHEN DAY_OF_WEEK(${start_raw}) = 0 AND DAY_OF_WEEK(${stop_raw}) != 0
THEN 1 ELSE 0 END);;
}

Adapted for Netezza

sum(distinct case when a12.ORDER_CURRENT_STATUS_CODE in (‘SHIPPED’) THEN dbods01…days_between( date(a12.ORDERDATE_PDT), date(pa11.WJXBFS1)) - ((FLOOR(dbods01…days_between (date(a12.ORDERDATE_PDT), date(pa11.WJXBFS1)) / 7) * 2) + CASE WHEN extract(dow from date(a12.ORDERDATE_PDT)) - extract(dow from date( pa11.WJXBFS1)) IN (2, 3, 4, 5,6) AND extract (dow from date( pa11.WJXBFS1)) <> 1 THEN 2 ELSE 0 END + CASE WHEN extract(dow from date(a12.ORDERDATE_PDT)) <> 1 AND extract(dow from date(pa11.WJXBFS1)) = 1 THEN 1 ELSE 0 END + CASE WHEN extract(dow from date(a12.ORDERDATE_PDT)) = 1 AND extract(dow from date(pa11.WJXBFS1)) <> 1 THEN 1 ELSE 0 END
)
ELSE NULL END)

BigQuery (Standard SQL):

DATETIME_DIFF(cast(${end_time} as datetime),cast(${start_time} as datetime), DAY) -
          ((FLOOR(DATETIME_DIFF(cast(${end_time} as datetime), cast(${start_time} as datetime), DAY) / 7) * 2) +
          CASE WHEN (${start_day_of_week_index} - ${end_day_of_week_index}) IN (1, 2, 3, 4, 5) AND ${end_day_of_week_index} != 0
          THEN 2 ELSE 0 END +
          CASE WHEN ${start_day_of_week_index} != 0 AND ${end_day_of_week_index} = 0
          THEN 1 ELSE 0 END +
          CASE WHEN ${start_day_of_week_index} = 0 AND ${end_day_of_week_index} != 0
          THEN 1 ELSE 0 END)

Here’s an alternative solution (Standard SQL):

DATE_DIFF(resolved_date,created_date,DAY) - 
DATE_DIFF(resolved_date, created_date,WEEK(SATURDAY)) -
DATE_DIFF(resolved_date, created_date,WEEK(SUNDAY))

Although I find that this results in N+1 to the Google Sheets (haven’t tested in Excel) results of NETWORKDAYS function.

Ex: 7/5/19 - 9/4/19
NETWORKDAYS: 44
Above solution: 43

Seems like NETWORKDAYS in Google sheets includes both the start date and the end date, so Today + Tomorrow returns 2 days, Mon-Wed returns 3 days so the “between” description is a little ambiguous.

4 posts were split to a new topic: GROUP BY error counting only weekdays between two dates

Top Labels in this Space
Top Solution Authors