Add to date (workdays (so exclude Sat & Sun), and exclude bank hols))

[Anonymous]

I have a simple table that is capturing holday bookings. I have the start date (&time) of the booking and the number of days & hours for each booking. When the days booked is greater than 1 day I need to calculate the end date (working day).

I have a separate table in the model with the bank holidays (Date & HolidayName) if needed. This is soooo easy in excel.

I know simlar questions have been asked but none seem to address this simple challenge.

Any Help would be Truly Appreciated (note data below is samlple not real)

Rgds  Adrian

[v-yetao1-msft · Accepted solution]

Hi @Anonymous 

Please correct me if I wrongly understood your question.

According to your description, the returned value has three cases:

(1)If the value of 'Table'[WholeDays] is less than 1 , the End date can be blank;

(2)If the result of 'Table'[Start Date] +'Table'[WholeDays]+ 'Table'[HoursPlus] is this week’s weekend or next week’s working day, then the date will be postponed 2 days later .Because you said you need calculate the end date (working day).

(3)If the result of 'Table'[Start Date] +'Table'[WholeDays]+ 'Table'[HoursPlus] is this week’s working day , return the result directly .

I create a measure that may meet your needs :

End Date =

var _backdays=WEEKDAY(MAX('Table'[Start Date]),2)

var _backdays2=_backdays+SELECTEDVALUE('Table'[WholeDays])

var _EndDate=SELECTEDVALUE('Table'[Start Date])+SELECTEDVALUE('Table'[WholeDays])+SELECTEDVALUE('Table'[HoursPlus])

return IF(SELECTEDVALUE('Table'[WholeDays])<1,BLANK(),IF(_backdays2<6,_EndDate,_EndDate+2))

The effect is as shown:

Notice : The data type of 'Table'[HoursPlus] must be “Time”

I have attached my pbix file, you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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

[amitchandak]

@Anonymous , I have a file where I have used the date table to calculate workdays. This should be the same as your table with holiday marked

refer if the file attached after signature can help , check 2nd page /work day

I also created a video on same -https://www.youtube.com/watch?v=Qv4wT8_P-AA

[Anonymous]

Hi, the video refers to calculating date difference which is not what I am trying to do. I am trying to add days to a date (exluding Sat, Sun, & Public Holidays).  Do you have something more specif to adding days to a date?

Thx Adrian

PREVIEW