How to count number of employees from joing date

[Hossam Ahmed]

Dear gents,

Can you please help me to find an equation to count the number of employees from joining date.

For example i have 2 columns first one includes names of employees and second column contain the joining date, i need to add third column to count the number of employees when i reach to specific date, so the equation should count the new hired employee in addition to the previous, so if the last cell count that we have 5 employees so next cell should count 6 1 new + 5 previous).

Here is an example:

Columns will be

Name.joining date. count

X1 . 1/1/2021 . 1

X2 . 2/1/2021 . 2 (1 previous +1new)

X3 . 3/1/2021 . 3 (2 previous + 1 new)


Thanks alot

 

[Joe4]

Welcome to the Board!

Let's say that you have the first one in cell C2 (so you have a "1" in cell C2).
Then, if cell C3, you can simply use the formula:

=C2+1

and copy down for all your rows.

If you wanted to check for the date in cell B3 first (in case you are copying down this formula before entering in all your data), you could use this version:

=IF(B3<>"",C2+1,"")

 

[jtakw]

Hi,

Do you mean something like this:

Book3.xlsx
	A	B	C
1	Name	Join Date	Count
2	X1	1/1/2021	1
3	X2	1/2/2021	2
4	X3	1/3/2021	3
5	X4	1/4/2021	4
6	X5	1/5/2021	5
7	X6	1/6/2021	6
8	X7	1/7/2021	7
9	X8	1/8/2021	8
10	X9	1/9/2021	9
11	X10	1/10/2021	10
12	X11
13
Sheet1050

Cell Formulas
Range	Formula
C2:C13	C2	=IF(B2="","",COUNTIF(B$2:B2,"<>"))

 

[Hossam Ahmed]

Welcome to the Board!

Let's say that you have the first one in cell C2 (so you have a "1" in cell C2).
Then, if cell C3, you can simply use the formula:

=C2+1

and copy down for all your rows.

If you wanted to check for the date in cell B3 first (in case you are copying down this formula before entering in all your data), you could use this version:

=IF(B3<>"",C2+1,"")

Welcome to the Board!

Let's say that you have the first one in cell C2 (so you have a "1" in cell C2).
Then, if cell C3, you can simply use the formula:

=C2+1

and copy down for all your rows.

If you wanted to check for the date in cell B3 first (in case you are copying down this formula before entering in all your data), you could use this version:

=IF(B3<>"",C2+1,"")

Thank you dear for your reply

Hi,

Do you mean something like this:

Book3.xlsx
	A	B	C
1	Name	Join Date	Count
2	X1	1/1/2021	1
3	X2	1/2/2021	2
4	X3	1/3/2021	3
5	X4	1/4/2021	4
6	X5	1/5/2021	5
7	X6	1/6/2021	6
8	X7	1/7/2021	7
9	X8	1/8/2021	8
10	X9	1/9/2021	9
11	X10	1/10/2021	10
12	X11
13
Sheet1050

Cell Formulas
Range	Formula
C2:C13	C2	=IF(B2="","",COUNTIF(B$2:B2,"<>"))

Yes exactly, that is what i need. What should i add between the two ""

 

[jtakw]

Thank you dear for your reply

Yes exactly, that is what i need. What should i add between the two ""

You don't need to add anything if you want the C cell Blank when there's no Date in B, or you can add anything you want (e.g. "Not yet joined")

 

[Hossam Ahmed]

Welcome to the Board!

Let's say that you have the first one in cell C2 (so you have a "1" in cell C2).
Then, if cell C3, you can simply use the formula:

=C2+1

and copy down for all your rows.

If you wanted to check for the date in cell B3 first (in case you are copying down this formula before entering in all your data), you could use this version:

=IF(B3<>"",C2+1,"")

Thank you joe, but what should i add between the first two " "

 

[Hossam Ahmed]

You don't need to add anything if you want the C cell Blank when there's no Date in B, or you can add anything you want (e.g. "Not yet joined")

Thanks alot dear ? i will try it and will tell you what happened smile ?

 

[jtakw]

Thanks alot dear ? i will try it and will tell you what happened smile ?

Just to be Clear, do Not add anything to the first "" in my formula, you can add anything you want to the Second "" you like.

 

[Hossam Ahmed]

Just to be Clear, do Not add anything to the first "" in my formula, you can add anything you want to the Second "" you like.

I tried it, and it is working but when I am doing any kind of sort, it starts counting from 1 again although the cell should maintain the original number because there are people hired before and after the mentioned date. is there any way to link it with max and min date, by this way it will not change?

 

[Joe4]

I tried it, and it is working but when I am doing any kind of sort, it starts counting from 1 again although the cell should maintain the original number because there are people hired before and after the mentioned date. is there any way to link it with max and min date, by this way it will not change?

Formulas are dynamic, so sorting it after you enter all the data could change those values.
If you want the value to be "hard-coded" and never changing for a person, then you may need to use VBA.

Here is VBA code that should do what you want, to column C.
This MUST be placed in the Sheet module. An easy way to get there is to right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor windows that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub

'   Exit if column B is not the column being updated
    If Target.Column <> 2 Then Exit Sub
    
'   Check to see if column C has a value
    If Target.Offset(0, 1) = "" Then
'       Find maximum value in column C and add 1
        Target.Offset(0, 1) = Application.WorksheetFunction.Max(Range("C:C")) + 1
    End If
    
End Sub

This code will automatically populate column C when you update column B by finding the maximum value in column C and adding 1.