1 2 Previous Next 17 Replies Latest reply on May 30, 2017 11:31 AM by Shinichiro Murakami

# Actual Working days / Total Working days of the month

Hi there,

I'm trying to calculate the number of actual working days (Simply Monday to Friday, based on today's date) divided by the total working days we will have for the month.

The idea is to get a % of completion.

Any idea how to do it?

Thnak you

• ###### 1. Re: Actual Working days / Total Working days of the month

Hi Morgan,

Find my approach as reference below and stored in attached workbook version 9.3 located in the original thread

1. Define weekdays based on date

2. Count weekdays: count([date (Weekdays)])

3. Count workingdays: if [date (Weekdays)]>=2 and [date (Weekdays)]<=6 then 1 END

4. % of: sum([Count Working days])/[Count Weekdays]

Regards,

Norbert

• ###### 2. Re: Actual Working days / Total Working days of the month

Tableau Desktop assigns numerical values 1 for Sunday, 2 for Monday,... and 7 for Saturday to days of the week. So

IIF((DATEPART('weekday',[Order Date]) <> 1 AND DATEPART('weekday',[Order Date]) <> 7) = TRUE, [OrderDate],Null)

will exclude Sunday and Saturday.

To count only workdays:

COUNT(DATEPART('weekday',(IIF((DATEPART('weekday',[Order Date]) <> 1 AND DATEPART('weekday',[Order Date]) <> 7) = TRUE, [Order Date],Null))))

To count all days

COUNT(DATEPART('weekday',[Order Date])

If you want to see only for the current month given today, then filter with:

(DATEPART('year', TODAY())*100 + DATEPART('month', TODAY())

• ###### 3. Re: Actual Working days / Total Working days of the month

Do you think it is possible not to link it to [Order Date] as I might not have daily entries?

As of today, we are the 29th May, so the 21st weekday, out of 23 weekdays for the full month.

I have not figured this out...

Thank you!

• ###### 4. Re: Actual Working days / Total Working days of the month

Hi Morgan

See attached link to understand a way and  a logic.

Excel's Networkdays Alternative (Count days excluding Weekend) [version 2]

Thanks,

Shin

• ###### 5. Re: Actual Working days / Total Working days of the month

Thank you for this great sharing.

Quite interesting.

However I don't want to rely on Start Date / End Date, but based on Today()

today() to get the number of weekdays as of now in the month (21) and today() to get the month and the total weekdays of the month (23)

I think I can get the 21 but for the 23, I don't know how...a month might be end the 31st, or 30th or 28/29...

I hope I'm clear.

Thank you

• ###### 6. Re: Actual Working days / Total Working days of the month

Hi Morgan,

Please check once again my workbook

Current month= may 2017 has 31 days & 23 workings days

Regards,Norbert

• ###### 7. Re: Actual Working days / Total Working days of the month

Thank you Norbert, may be I am misunderstanding, but you have to create a table with [date] with April and May dates in your case, correct?

In my case I do have a field date, but only as of now (21) since no records yet for tomorrow and the day after. How can i generate them or count the total numbers of weekdays for may?

Thanks a lot!

• ###### 8. Re: Actual Working days / Total Working days of the month

Hi Morgan,

You could achieve this based on scaffolding Technic explained here

Regards,

Norbert

• ###### 9. Re: Actual Working days / Total Working days of the month

Interesting one!

Thank you, I will give it a try.

• ###### 10. Re: Actual Working days / Total Working days of the month

Hi Morgan,

Played around with the scaffold scenario but not able to overcome the level of detail issue here.

Would like to ask Simon Runc to the table. Could you help us out on this one.

Upfront thanks again for your attention.

Regards,

Norbert

• ###### 11. Re: Actual Working days / Total Working days of the month

Morgan,

Excel's Networkdays Alternative (Count days excluding Weekend) [version 2]

Using exact same logic, you only need to define start and end date as follows

Thanks,

Shin

2 of 2 people found this helpful
• ###### 12. Re: Actual Working days / Total Working days of the month

Hi Shin,

Thanks for stepping in but what I understood  is that within a (current) month not all days are already in the database. e.g. Current month up to today day 1-28 are in the database. So how to calculate all days voor current month?

Regards,

Norbert

• ###### 13. Re: Actual Working days / Total Working days of the month

Hi Nobert,

The start date is defined as the first date of month and end date is defined as "today" and "End of month (of today)"

There are multiple line items there and "sum" does not work but "min" or "max" whatever of aggregation works to get expected numbers?

Thanks,

Shim

1 of 1 people found this helpful
• ###### 14. Re: Actual Working days / Total Working days of the month

Hi Morgan,

I think you can get what you need without padding your data, although it is a little complex and my solution is not fully tested.

What we'll do is use the function TODAY to return today's date, then DATETRUNC today to get the first day of the month, then use DATETRUNC and DATEADD to get the last day of the month.

We then would use DATEDIFF(days) to determine the total number of days in the month.  Since we only want weekdays we will have to subtract the number of weekend days in the month, we do this by using DATEDIFF(weeks) and multiplying by 2.  We also need to make a field to adjust our count if the month starts on a Sunday we subtract 1 or if it ends on a Saturday we add 1.

Below is the logic I wrote:

Days in Current Month:

Weeks in Current Month:

Saturdays:

IF   DATEPART('weekday', DATEADD('month',1,DATETRUNC('month', TODAY())))-1 = 7 THEN  1

ELSE 0

END

Sundays:

IF DATEPART('weekday', DATETRUNC('month', TODAY())) = 1 THEN -1

ELSE 0

END

Workdays in Current Month:

[Days in Current Month]-([Weeks in Current Month]*2) + [Sundays]+[Saturdays]

Now we need to do similar calculations based on TODAY rather than EOM.  The logic will need to be modified if you need to run the report on Saturdays

Days in Current Month til Today:

DATEDIFF('weekday',DATETRUNC('month', TODAY()),TODAY()+1 )

Weeks in Current Month til Today:

DATEDIFF('week',DATETRUNC('month', TODAY()),TODAY() )

Workdays in current Month til Today:

[Days in Current Month til Today]-([Weeks in Current Month til Today]*2) + [Sundays]

You can then create your Percent Complete: [Workdays in current MonthTil Today]/[Workdays in current Month]

Let me know if you have any questions.

Regards,
Ivan

1 of 1 people found this helpful
1 2 Previous Next