Any formula that can help me to calculate the number of the employee
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 30Nov2021] & [As at 31Dec2021] 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 31Jan2021] all the way to [As at 31Oct2021] will be at "1". Then, [As at 30Nov2021] to [As at 31Dec2021] will be at "0".
Thanks in advance for the advices & help! 😊
Answers

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)
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.9K Get Help
 379 Global Discussions
 210 Industry Talk
 441 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 300 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!