Question

# How to count only weekdays between two dates

• 6 replies
• 855 views

Userlevel 3
• Looker Staff
• 51 replies

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.

### 6 replies

Userlevel 3

###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);;
}``````

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.

Userlevel 7
+1

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