Hi, I have. Wanted to reply to you yesterday but i got some problems using BB code. (I'm new to this).
First I wanted to ask you: do you need the status cells?
Because if you don't have termination date every you know that you have to calculate FTE.
If you have a termination date, the days after the termination date are not added.
If you have a LOA Start and End date, the days that are part of LOA are not added.
If you do this, you wont need the status.
For every month i added the total days that within the "Hire Date - Terminate Date" time span, not considering those that fall between the LOA start and end dates, and dividing this number of "worked days" by the total number of days in the month. I'm not sure if that is correct, or if you want to just consider working days, for example from monday to friday. If this is the case we would have to make some minor changes.
I'll pase my BB code here. And i'll add manually the named ranges used:
If you are in the cell H5:
CurrentMonth: =MONTH(Hoja1!H$4)
DaysInMonth: =EDATE(Hoja1!H$4,1)-Hoja1!H$4
HireDate: =Hoja1!$C5
LoaEndDate: =Hoja1!$F5
LoaStartDate: =Hoja1!$E5
TerminateDate: =IF(Hoja1!$D5=0,1000000,Hoja1!$D5)
Let me know if that is what you need or if we need to make some changes.
| FTE.xlsx |
|---|
|
|---|
| C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S |
|---|
| 4 | Hire date | Terminate date | LOA start date | LOA end date | Status | ene-24 | feb-24 | mar-24 | abr-24 | may-24 | jun-24 | jul-24 | ago-24 | sep-24 | oct-24 | nov-24 | dic-24 |
|---|
| 5 | 2022-08-08 | | | | Active | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
|---|
| 6 | 2022-08-08 | 2024-04-15 | | | Terminated | 1 | 1 | 1 | 0,5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
|---|
| 7 | 2022-08-08 | | 2023-09-15 | 2024-09-14 | LOA | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0,53333333 | 1 | 1 | 1 |
|---|
|
|---|
