FTE Calculation - Partial Month

MCorbin

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???

MCorbin

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!