Any formula that can help me to calculate the number of the employee

HC. Tan

Hi all,

I am currently stumped by which formula to use in order for me to calculate the number of the employee.

As you can see from the screenshot below, I was planned to calculate the movement of the employee in monthly basis.

So, currently the formula that I using is:

=COUNT([Joined Date]@row) - COUNTIFS([Last Working Date]@row, "(Month) 2021", [Last Working Date]@row, "(Month) 2021")

For example, this employee last working date is November 2021, so I expecting to see the cells in [As at 30-Nov-2021] & [As at 31-Dec-2021] will be "0".

So, the final result that I wanted is if the employee last working date is "November 2021", I expect to see from [As at 31-Jan-2021] all the way to [As at 31-Oct-2021] will be at "1". Then, [As at 30-Nov-2021] to [As at 31-Dec-2021] will be at "0".

Thanks in advance for the advices & help! 😊

Devin Lee

Hey @HC. Tan,

Your first issue is that the column header holds the date value but you can't reference that date. Those column types need to be text/number so the column date will need to be referenced from another column with a unique text/number.

To do this we create two new columns. One with the dates and the other with a list of unique ID's. In our case lets just make it numerical counting up starting with 1 and call it the Month ID. In the Date column we can start with the first Month listed in your sheet. I'll start with 01/31/22. From there you can input a formula into Date2 and pull in the last day of each month and pull that downwards to fill in the column

=IF(MONTH(Date1) = 11, DATE(YEAR(Date1) + 1, MONTH(Date1) - 10, 1) - 1, IF(MONTH(Date1) = 12, DATE(YEAR(Date1) + 1, MONTH(Date1) - 10, 1) - 1, (DATE(YEAR(Date1), MONTH(Date1) + 2, 1)) - 1))

Now you need to add a row at the top of the sheet to hold the Month ID so the formula can replace the Month ID with the correct Date. Just enter in the ID's as shown in the image below. Then input the formula into the As at Month columns

=IF(AND($[Last Working Date]@row > INDEX(Date:Date, MATCH([As at Month Year 1]$1, [Month ID]:[Month ID], 0), 0), $[Joined Date]@row < INDEX(Date:Date, MATCH([As at Month Year 1]$1, [Month ID]:[Month ID], 0), 0)), 1, 0)