Forum Discussion
How can I create a column with a datedif only between working days?
I want to create a column that is a DateDif between today (current day) and another column of random dates (X column) without including weeknds (Saturday & Sunday).
For example, if today 05/20/2021
| X Column | DateDif vs Current day (wo Saturday and Sunday) |
| 05/21/2021 | 1 |
| 05/24/2021 | 2 |
| 05/25/2021 | 3 |
| 05/31/2021 | 7 |
How can I create this DateDif Column?
As an extra info, X Column never is going to have a date on Saturday or Sunday.
I hope you can help me, and thnks in advantage,
Hi jmontes1810 ,
You can simply use datediff minus weekdiff*2.
Check the formula, today() is 5/25/2021.
Column = DATEDIFF(TODAY(),'Table'[X Column],DAY)-(WEEKNUM('Table'[X Column])-WEEKNUM(TODAY()))*2Best Regards,
Jay
2 Replies
Maybe this post will help you:
https://community.powerbi.com/t5/Desktop/DATEDIFF-excluding-weekends/m-p/947915
or
Create a new column
IsWeekend =IF(WEEKDAY('Table'[Date],2)>5,FALSE,TRUE)
and then the datediff calculation will be the following
Difference =
DATEDIFF([StartDateTime],[EndDateTime], DAY) - ( CALCULATE ( COUNTROWS('Table'), 'Table'[IsWeekend] = TRUE(), DATESBETWEEN('Table'[Date], [StartDateTime],[EndDateTime]) ) )
Hi jmontes1810 ,
You can simply use datediff minus weekdiff*2.
Check the formula, today() is 5/25/2021.
Column = DATEDIFF(TODAY(),'Table'[X Column],DAY)-(WEEKNUM('Table'[X Column])-WEEKNUM(TODAY()))*2Best Regards,
Jay
