cancel
Showing results for
Search instead for
Did you mean:
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
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.

5 REPLIES 5
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.

Frequent Visitor

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 number Item Creation date Confirmation date 1 Confirmation date 2 Cycle time in hours comment 12345678 1 25 june 2021 28 june 2021 here its not recognizable that saturday was a working day, so it calculates the cylce time only Friday & Monday 12345679 5 26 june 26 june Confirmation date 2  is a saturday so the calculation is correct 12345689 1 26 june 28 june Confirmation date is a monday so the calculation is false even though it was created on a saturday 12345789 3 24 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
Community Support

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

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.

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
Want To Learn Power BI
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

#### Check it Out!

Click here to read more about the December 2021 Updates!

#### Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

#### Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors