FTE Calculation - Partial Month

Options
MCorbin
MCorbin Overachievers Alumni

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

  • MCorbin
    MCorbin Overachievers Alumni
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!