Total number of weekdays in a month?

HowTo
#1

Hello, I am working on automating my monthly timesheet allocations and one of the early steps is to simply calculate the number of weekdays in a given month.

My monthly timesheet (which provides the details for each task) has been imported into Exploratory and the number of hours has been summarized by project name.

image
image.png1313×587 39.9 KB

The goal is to be able to multiply the number of weekdays in January 2021 by 8 to calculate the expected hours, and then split the overhead hours over the other three categories proportionally.

What is a good way to accomplish this goal?

(This is a process I have been doing in Excel for years, but I would love to just be able to import the timesheet and run a script for each month in 2021.)

Thanks in advance!

#2

This would do the trick.

sum(weekend(seq(as.Date(“2021-01-01”), as.Date(“2021-01-31”), “days”)) == “Weekday”)

you can use ‘ymd’ function instead of ‘as.Date’, they do the same thing but I personally prefer ‘ymd’ because it’s more flexible.

sum(weekend(seq(ymd(“2021-01-01”), ymd(“2021-01-31”), “days”)) == “Weekday”)

The ‘weekend’ function returns ‘Weekend’ if a given date is either Sunday or Saturday, otherwise ‘Weekday’.

And, if you want to fully automate this based on the ‘current’ month, then you can do something like this! :fire:

sum(weekend(seq(floor_date(today(), unit=“month”), ceiling_date(today(), unit=“month”) - days(1), “days”)) == “Weekday”)

Make sure you do this with ‘Create Calculation’ step with your example.

1 Like
#3

Kan, this is so helpful.

I suppose because I am on a windows machine (?) it worked when I used a single quote instead of a double.

image
image.png1098×435 25.4 KB

This got me the number of weekdays in January, and then I was able to go from there. The goal is def to automate the whole process so I can’t wait to try integrating your third recommendation.

Thanks!

#4

On a related note, please tell me, how can I reference the total value for the Hours_To_Split column in this equation?

image
image.png1152×805 43 KB

#5

I asked too soon! Just figured it out based on this earlier post:

image
image.png1242×906 55.2 KB