3 Replies Latest reply on Jun 15, 2015 6:33 PM by Nicholas Hannan

# How to create a formula for X out of X business days?

Hi everyone,

I have a company dashboard that looks like this (marks removed for privacy):

Right underneath the date, I want it to say how many business days we are into the month, out of how many total business days exist for the month.

I can calculate how many business days we have had so far with this:

If [Day Of Week]=2 or [Day Of Week]=3 or [Day Of Week]=4 or [Day Of Week]=5 or [Day Of Week]=6

//If it's Monday-Friday

then [Day Of Month] end

The problem is, I can't get the list of how many business days exist in the month altogether because the [Date] only counts how many days have occurred so far.

So how do I calculate how many total business days there are this month when this month isn't over?

I tried something like this:

DATEDIFF('day', DATETRUNC('month',today()),DATETRUNC('month',DATEADD('month',1, today() )))

This SHOULD have said "How many days are there between the Datetrunc of this month versus the Datetrunc of last month" because ideally it should have given me the total number of days this month, but for some reason the answer comes out as only 1.

Thank you in advance for your help

• ###### 1. Re: How to create a formula for X out of X business days?

Hi Ailsa,

Please check this:

Calculating the Number of Business Days

You can of course get the start of the month with:

DATETRUNC('month', <your date>)

And the last day of the month with:

DATEADD('day', -1, DATETRUNC('month', DATEADD('month', 1, <your date>)))

Note, I did also just test your calculated field that was returning 1 for you, but it returned 30 for me. Are you sure that is the version that was giving you 1?

Best regards.

1 of 1 people found this helpful
• ###### 2. Re: How to create a formula for X out of X business days?

Hi Nick,

The link didn't help me because it only calculates finished months, not future months.

I did, however, select "Maximum" on the drop-down list of my original calculation, which now comes out to 30 (the correct number of days in June) but now I have to remove the weekends and possible holidays.

• ###### 3. Re: How to create a formula for X out of X business days?

Hi Ailsa,

Sorry, you did not specify that in your original post. You should still be able to employ the logic the article uses in your calculation instead of trying to re-create the wheel.

If you need something to calculate for the future, consider extending the end date to reflect that. If that does not work, can always include X number of blank records in your datasource for future months you need. Doing that would be very simple and it would be simple to filter out from other views as well.

Best regards.