FTE Calculation - Partial Month
OK Smartsheet Formula Nerds... I know you're out there....
I am working on a staffing model. I need to calculate the number of FTEs per month based on FTE Start and Term dates.
I have all of the FTEs listed in a team sheet that looks like this:
Right now, I'm doing a COUNTIFS that is matching based on the Team Name (which is a helper column in my Staffing Model sheet) and comparing the Start and Term dates to the 1st and last day of the month:
=COUNTIFS({HIT Team}, $Team@row, {TM Start}, <=DATE([Column2]$2, [Column2]$3, [Column2]$4), {TM Term}, ISBLANK(@cell)) + COUNTIFS({HIT Team}, $Team@row, {TM Start}, <=DATE([Column2]$2, [Column2]$3, [Column2]$4), {TM Term}, >=DATE([Column2]$2, [Column2]$3, 1)) - 1
The problem I'm running into is when a Team member either left the team mid-month or started on the team mid-month. I don't necessarily want that to count as a whole FTE.
In the 2nd screenshot - this team had 2 TMs who started 8/15, and 2 who term'd 8/16. These should essentially cancel each other out for no net change in TM count that month, but my formula is counting it as 2 extra TMs for August.
So I realize Countifs is not the right function.... but because it's 4:59 PM on a Friday, I'm completely unable to think about how I should approach this....
How about it Formula Gurus...... Got any ideas???
Comments
-
Well, I answered my own question.... BUT..... I'm notorious for finding the long way around a problem, so if you've got a more efficient way of doing this, I'd love to hear about it!!!
Did I mention that I need to calculate these over a rolling 12 month period? Oh yeah, that made it that much more fun... hahaha!
I added columns in the Team Member sheet so that I could calculate if that TM was active 1 month ago, 2 months ago, 3 months ago... (out to 12 months). I needed to capture all of the following criteria:
· If Term Date is before the 1st of the month, 0
· If Start Date is <= 1st of the month AND End Date is blank, 1
· If Start Date is <= 1st of the month AND End Date is >=Last day of month, 1
· If Start Date is <= 1st of the month AND End Date is <=Last day of month, Calculate Net Working Days(1st of month, Term Date) / networkingdays(1st of month,last of month)
· If Start Date is >= 1st of the month AND End Date is <=Last day of month, Calculate Net Working Days(Start Date, Term Date) / networkingdays(1st of month,last of month)
· If Start Date is > 1st of month and < end of month, calculate Net working days (Start Date, End of Month) divided by Net working Days (1st of month, End of month)
I needed to make these column formulas, so unfortunately, I couldn't add helper rows into the sheet, which I normally do when I'm calculating rolling 12 month formulas... So that made it even more fun.
I always start with a test date.... I make this date the 15th of the current month. That way, I can add 30, 60, 90 days (etc) to the test date to get 1 month ago, 2 months ago, 3 months ago and so on:
1st Day of the month - 1 month ago: DATE(YEAR([Test Date]@row - (30 * 1))
Last Day of the month - 1 month ago: DATE(YEAR([Test Date]@row - (30 * 1)), MONTH([Test Date]@row - (30 * 1)), INDEX({last day}, MATCH(MONTH([Test Date]@row - (30 * 1)), {Month Num}, 0))))
(The Index/Match formula looks up to a sheet where I store the last day of every month). I could have made this formula more complicated by adding leap years in (oh crap.... I meant to do that).... Yeah, not gonna go back.
So, with that in mind, here's the full formula:
=IF(AND(ISDATE([Term Date]@row), [Term Date]@row < DATE(YEAR([Test Date]@row - (30 * 1)), MONTH([Test Date]@row - (30 * 1)))), 0, IF(AND([Start Date]@row >= DATE(YEAR([Test Date]@row - (30 * 1)), MONTH([Test Date]@row - (30 * 1)), 1), ISDATE([Term Date]@row), [Term Date]@row <= DATE(YEAR([Test Date]@row - (30 * 1)), MONTH([Test Date]@row - (30 * 1)), INDEX({last day}, MATCH(MONTH([Test Date]@row - (30 * 1)), {Month Num}, 0)))), NETWORKDAYS([Start Date]@row, [Term Date]@row) / NETWORKDAYS(DATE(YEAR([Test Date]@row - (30 * 1)), MONTH([Test Date]@row - (30 * 1)), 1), DATE(YEAR([Test Date]@row - (30 * 1)), MONTH([Test Date]@row - (30 * 1)), INDEX({last day}, MATCH(MONTH([Test Date]@row - (30 * 1)), {Month Num}, 0)))), IF(AND([Start Date]@row <= DATE(YEAR([Test Date]@row - (30 * 1)), MONTH([Test Date]@row - (30 * 1)), 1), ISBLANK([Term Date]@row)), 1, IF(AND([Start Date]@row <= DATE(YEAR([Test Date]@row - (30 * 1)), MONTH([Test Date]@row - (30 * 1)), 1), [Term Date]@row >= DATE(YEAR([Test Date]@row - (30 * 1)), MONTH([Test Date]@row - (30 * 1)), INDEX({last day}, MATCH(MONTH([Test Date]@row - (30 * 1)), {Month Num}, 0)))), 1, IF(AND([Start Date]@row <= DATE(YEAR([Test Date]@row - (30 * 1)), MONTH([Test Date]@row - (30 * 1)), 1), [Term Date]@row <= DATE(YEAR([Test Date]@row - (30 * 1)), MONTH([Test Date]@row - (30 * 1)), INDEX({last day}, MATCH(MONTH([Test Date]@row - (30 * 1)), {Month Num}, 0)))), NETWORKDAYS(DATE(YEAR([Test Date]@row - (30 * 1)), MONTH([Test Date]@row - (30 * 1)), 1), [Term Date]@row) / NETWORKDAYS(DATE(YEAR([Test Date]@row - (30 * 1)), MONTH([Test Date]@row - (30 * 1)), 1), DATE(YEAR([Test Date]@row - (30 * 1)), MONTH([Test Date]@row - (30 * 1)), INDEX({last day}, MATCH(MONTH([Test Date]@row - (30 * 1)), {Month Num}, 0)))), IF(AND([Start Date]@row > DATE(YEAR([Test Date]@row - (30 * 1)), MONTH([Test Date]@row - (30 * 1)), 1), [Start Date]@row <= DATE(YEAR([Test Date]@row - (30 * 1)), MONTH([Test Date]@row - (30 * 1)), INDEX({last day}, MATCH(MONTH([Test Date]@row - (30 * 1)), {Month Num}, 0)))), NETWORKDAYS([Start Date]@row, DATE(YEAR([Test Date]@row - (30 * 1)), MONTH([Test Date]@row - (30 * 1)), INDEX({last day}, MATCH(MONTH([Test Date]@row - (30 * 1)), {Month Num}, 0)))) / NETWORKDAYS(DATE(YEAR([Test Date]@row - (30 * 1)), MONTH([Test Date]@row - (30 * 1)), 1), DATE(YEAR([Test Date]@row - (30 * 1)), MONTH([Test Date]@row - (30 * 1)), INDEX({last day}, MATCH(MONTH([Test Date]@row - (30 * 1)), {Month Num}, 0)))), 0))))))
And for each month (this is for 1 month ago), I had to copy the formula and update it for the month I'm calculating for.... So for 2 months ago, I had to go find each (30 * 1) part of the formula and change it to (30*2). Normally, this is one of the things I would use a helper row for. I missed my helper row! And I'm a bit embarrassed to say that it took me 9 months before I thought to copy the formula into Word and do a search and replace. Oops! (The last 3 months went much faster after that).
So now I've got my team list showing each TM's FTE calculation by month for the last 12 months:
Back to my Staffing sheet - I updated my formula to sum the appropriate column (12 months ago, 11 months ago, etc) if the Team matches the Team in the Staffing sheet.
=SUMIF({TM Team}, $Team@row, {12 Mo}) - 1
(The -1 removes the supervisor, who we don't want to count)
Note: This mean I had to customize the formula for every column - I hate that... I much prefer using helper columns so I can create a formula once, then copy and paste. But that was not to be :-(
SO..... If you've got a more efficient way to come up with these numbers.... I'd soooo love to hear it!
Happy Sunday!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!