
1. Re: Calculating a pace??
Tracy Rodgers Apr 24, 2012 10:18 AM (in response to dhveryoldaccount)1 of 1 people found this helpfulHi Derek,
A calculation using the datediff calculation to find the '# of work days to date ' could be used. The formula might look similar to the following:
datediff('day', [date field], today())
This will return the number of days between that starting date and today. I hope this helps!
Tracy

2. Re: Calculating a pace??
Richard Leeke Apr 24, 2012 11:31 AM (in response to dhveryoldaccount)1 of 1 people found this helpfulThis old thread discusses the issue of working days. I also remember another more general solution involving a separate calendar table so that individual holiday days could also be excluded  but didn't find that one with a quick search.

3. Re: Calculating a pace??
dhveryoldaccount Apr 26, 2012 1:07 PM (in response to Richard Leeke)Thanks for the helpful replies.
Here's what I did to find the maximum number of days in a given month.
day(date(datetrunc('month',date(datetrunc('month', today()))+32))1)
What this does is find the maximum number of days in the current month using the "today()" function inside the "datetrunc" function.
You can test the calculations by just changing "today()" to any specified date surrounded by "#". Like #2/15/2012#.
Now I need to find a way to count the number of Sundays in a given month. Thoughts?

4. Re: Calculating a pace??
Richard Leeke Apr 27, 2012 4:31 AM (in response to dhveryoldaccount)Attached workbook has a couple of slightly convoluted calculations that get you want you want (plus a slightly simpler version of counting days in a month).
The Sundays one just works by saying that the first 28 days of the month must have 4 Sundays and then working out whether the remainder includes a Sunday for each of the 4 possible month lengths (28, 29, 30 and 31 days).

Counting Days.twbx.zip 184.4 KB


5. Re: Calculating a pace??
dhveryoldaccount Apr 27, 2012 8:48 AM (in response to Richard Leeke)Thanks Richard! The Sunday calculation works great!
By taking the total number of days in a month minus the number of Sundays gives me the "work days available" for the month.
Now I need to find out how many of those work days I've used up to today.
Thoughts?

6. Re: Calculating a pace??
dhveryoldaccount Apr 27, 2012 9:06 AM (in response to dhveryoldaccount)Think I figured it out. Please check my work, but I think this solves my problem.
iif(day(today())<=7, (today()  datetrunc('month',today())1),
iif(day(today())<=14,(today()  datetrunc('month',today())2),
iif(day(today())<=21,(today()  datetrunc('month',today())3),
iif(day(today())<=28,(today()  datetrunc('month',today())4),
iif(day(today())<=31,(today()  datetrunc('month',today())5),
0)))))
+1

7. Re: Calculating a pace??
dhveryoldaccount May 11, 2012 9:26 AM (in response to dhveryoldaccount)Just an update. I've created another pace report for our "service" department. Sales and service have different working days. Sales works Monday  Saturday, service works Monday  Friday. Richards help on finding the number of Sundays in a month, a number I needed to exclude in order to calculate the correct number of "working" days in a month for sales, was invaluable. Now for service I needed to find the number of Saturdays as well, also to exclude from calculations.
Quickly, here is how to find Sundays in a given month:
4 +
CASE [Days in Month]
WHEN 28 THEN 0
WHEN 29 THEN IIF(DATEPART('weekday',[29th of Month])=1, 1, 0)
WHEN 30 THEN IIF(DATEPART('weekday',[29th of Month])=1 OR DATEPART('weekday',[29th of Month])=7, 1, 0)
WHEN 31 THEN IIF(DATEPART('weekday',[29th of Month])=1 OR DATEPART('weekday',[29th of Month])>=6, 1, 0)
END
Where [Days in Month] is: DATEADD('month', 1, DATETRUNC('month',today()))  DATETRUNC('month',today())
And where [29th of the Month] is: DATEADD('day', 28, DATETRUNC('month', today()))
To find Saturdays in a month I created this (based of Richard's "Sundays" calc):
4 +
CASE [Days in Month]
WHEN 28 THEN 0
WHEN 29 THEN IIF(DATEPART('weekday',[29th of Month])=7, 1, 0)
WHEN 30 THEN IIF(DATEPART('weekday',[29th of Month])=7 OR DATEPART('weekday',[29th of Month])=6, 1, 0)
WHEN 31 THEN IIF(DATEPART('weekday',[29th of Month])>=5, 1, 0)
END
Attached is an Excel representation for thinking about the 29th of the Month.

29th of the month.xlsx 10.3 KB
