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.
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!
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!
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.
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.
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.
On a related note, please tell me, how can I reference the total value for the Hours_To_Split column in this equation?
I asked too soon! Just figured it out based on this earlier post: