cancel
Showing results for
Did you mean:
Post Prodigy

## Calculate working Dates between two columns

I have a simple table (1) that contains a Date column. Then I have a second table (2) that contains all holidays also on Date level.

Is there any chance to determine the number of working days - excluding holidays - between the Date column of the first table and today?

If so, how?

1 ACCEPTED SOLUTION
Super User

@joshua1990

You need to add a custom column with the following M Code in Table 1.

``````List.Count(
if
[Date] > Date.From(DateTime.LocalNow())
then
{}
else

List.Select(
List.Difference(
List.Dates(
[Date],
Duration.Days
(
Date.From(DateTime.LocalNow()) - [Date]
) + 1
,
#duration(1, 0, 0, 0)
),
Holidays[Holiday]
),
each Date.DayOfWeek(_,Day.Monday) < 6
)
)``````

I have attached the file below.

Did I answer your question? Mark my post as a solution! and hit thumbs up
5 REPLIES 5
Community Champion

Frequent Visitor

I am trying to apply your solution to a simila problem I am having. I downloaded the attachment and followed your instructions, but it is not working. Can you please explain why you create the _Measures query and how it helps?

thank you,

Super User

@joshua1990

You need to add a custom column with the following M Code in Table 1.

``````List.Count(
if
[Date] > Date.From(DateTime.LocalNow())
then
{}
else

List.Select(
List.Difference(
List.Dates(
[Date],
Duration.Days
(
Date.From(DateTime.LocalNow()) - [Date]
) + 1
,
#duration(1, 0, 0, 0)
),
Holidays[Holiday]
),
each Date.DayOfWeek(_,Day.Monday) < 6
)
)``````

I have attached the file below.

Did I answer your question? Mark my post as a solution! and hit thumbs up
Post Prodigy

Thanks @Fowmy : Thanks a lot! But what is about the weekends?

I'm sorry that I missed to add this information:

Working days = Mo - Fr

Super User

@joshua1990

I added that condition as well also, attached the file after modification. Please check my 1st reply.

Did I answer your question? Mark my post as a solution! and hit thumbs up

Announcements