cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alex272
Frequent Visitor

How to calculate date difference excluding weekends except if there is a value

Hello everyone!

 

I need some help calculating the date difference between two dates.

 

I am trying to calculate the cycle time with different conditions. So usually its the date difference between creation and confirmation date without weekends, as nobody is working on sat&sun. Now there are some saturdays that are exceptionally working days and have to be taken into the calculation of the cycle times. 

 

My formula without any conditions that i habe to consider is like following: 

if(... if...(....,

 

DATEDIFF( creation date, confirmation date, HOUR) - (CALCULATE
(Countrows('Date'), 'Date'[Workday]=0, DATESBETWEEN('Date'[Date], creation date, confirmation date)
)*24
 
)))
how can i tranform it into: "if there are values(creation or confirmation dates) on a saturday, than consider this day as a workingday, otherwise its still a weekend"
 
Thanks for your help! 
1 ACCEPTED SOLUTION
v-janeyg-msft
Community Support
Community Support

Hi, @alex272 

 

Your idea is completely achievable, and it is not difficult, but I don't know what your data is like. Can you share some sample data, such as what is in the workday, weekday and holiday? So we can help you modify the formula.

 

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-janeyg-msft
Community Support
Community Support

Hi, @alex272 

 

Your idea is completely achievable, and it is not difficult, but I don't know what your data is like. Can you share some sample data, such as what is in the workday, weekday and holiday? So we can help you modify the formula.

 

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hey @v-janeyg-msft  , sorry I was out of office

so the data is basically like in that table, the other informartions are not really relevant: 

As there are confirmation & creation dates on saturday, this saturday was a working day.

There are also some restrictions so i will post my formula here, too. 

FYI my date table is related to the confirmation date 2

 

TO numberItemCreation dateConfirmation date 1Confirmation date 2Cycle time in hours comment
12345678125 june 2021 28 june 2021 here its not recognizable that saturday was a working day, so it calculates the cylce time only Friday & Monday 
12345679526 june 26 june Confirmation date 2  is a saturday so the calculation is correct
12345689126 june 28 june Confirmation date is a monday so the calculation is false even though it was created on a saturday
12345789324 june 26 june confirmation date is saturday -> working day, correct

hope that's understandable.

And here is my super complicated formula with all the restrictions 

 

cycle time =
if( ISBLANK('Data'[confirmation date 1]), 
if('Data'[confirmation date 2]>='Data'[creation] 
&&
DATEDIFF('Data'[creation],'Data'[confirmation date 2],HOUR) - (
if(RELATED(Date[Weekday])=6, calculate (countrows('Date'), Date[Weekday]=7, DATESBETWEEN('Date'[Date], 'Data'[creation], 'Data'[confirmation date 2]))*24, calculate (Countrows('Date'), 'Date'[Workday]=0, DATESBETWEEN('Date'[Date], 'Data'[creation], 'Daten neu'[Confirmation date 2])
)*24
)>=0 && not(ISBLANK('Data'[confirmation date 2]))),
DATEDIFF('Data'[creation],'Data'[confirmation date 2],HOUR) - (
if(RELATED(Date[Weekday])=6, calculate (countrows('Date'), Date[Weekday]=7, DATESBETWEEN('Date'[Date], 'Data'[creation], 'Data'[Confirmation date 2]))*24, CALCULATE
(Countrows('Date'), 'Date'[Workday]=0, DATESBETWEEN('Date'[Date], 'Data'[creation], 'Data'[Confirmation date 2])
)*24
)), 
BLANK()),
 
if( 'Data'[Creation date 1]>'Data'[creation date 2],
DATEDIFF('Dats'[creation],'Data'[confirmation date 1],HOUR) - ( 
if(RELATED(Date[Weekday])=6, calculate (countrows('Date'), Date[Weekday]=7, DATESBETWEEN('Date'[Date], 'Data'[creation], 'Data'[Confirmation date 1]))*24,
CALCULATE
(Countrows('Date'), 'Date'[Workday]=0, DATESBETWEEN('Date'[Date], 'Data'[creation], 'Data'[Confirmation date 1])
)*24)),
DATEDIFF('Data'[creation],'Data'[confirmation date 2],HOUR) - ( if(RELATED(Date[Weekday])=6 , calculate (countrows('Date'), Date[Weekday]=7, DATESBETWEEN('Date'[Date], 'Data'[Creation], 'Data'[confirmation date 2]))*24,
CALCULATE
(Countrows('Date'), 'Date'[Workday]=0, DATESBETWEEN('Date'[Date], 'Data'[Creation], 'Data'[Confirmation date 2])
)*24
))))
 
I really don't know how to simplify and do it correct 

Hi, @alex272 

 

I really want to help you. But, from the little sample data and comments you gave, the calculation logic isn't described clearly, and I don’t know how to judge when to add Saturday and when not to add. And I don’t know what the result you want, so...If you still need help, I think what you need to provide is part of the sample data that can be tested, as well as the calculation logic, and the desired result instead of the long code.

Reference:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

 

Best Regards

Janey Guo

 

alex272
Frequent Visitor

Thank you for your answer. 

 

Maybe i should have mentioned that i already have a date table with columns 'date', 'weekday', 'holiday' and 'workday'.

 

so i need somehow to use an if function  like that: 

 

if(... if...(....,

 

DATEDIFF( creation date, confirmation date, HOUR) - (if confirmation date or creation date are 'date'[Weekday]=6 and not blank, calculate weekdays 1to 6 (including weekday=6), otherwise CALCULATE
(Countrows('Date'), 'Date'[Workday]=0, DATESBETWEEN('Date'[Date], creation date, confirmation date)
)*24
 
)))

 

 

I just down know if something like that is possible and how to write it. 

amitchandak
Super User
Super User

@alex272 , You can get workday like

 

Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Creation date],Table[confirmation Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

 

How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.