EXCEL: CountIf with Date Values 1

[geo40]

COUNT-IF function.

I want to count if the employment date of a employee is within the date range. So if I want to see how many persons start working at our company in August, I have to insert a between construction in the count-if formula: between 01/08/2005 and 31/08/2005.

But the question is how. Any suggestion would be welcome.

Regards,

GEO40

 

[macropod]

Hi geo40,

For dates in column A:

=COUNTIF(A:A,">"&DATE(2005,8,0))-COUNTIF(A:A,">"&DATE(2005,9,0))

Cheers

 

[AnotherHiggins]

This should also work without needing to fuss with the date function:
[COLOR=blue white]=COUNTIF(A:A,">=8/1/05")-COUNTIF(A:A,">8/31/05")[/color]
(oops - European format)
[COLOR=blue white]=COUNTIF(A:A,">=1/8/05")-COUNTIF(A:A,">31/8/05")[/color]

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[KenWright]

And for clarity's sake, it is often better to put your dates into separate cells and then reference them from within the formula. This allows you at a glance to see the parameters of what you are pulling, and makes it very easy to change them when necessary, eg assuming your dates were in F1 and F2

=COUNTIF(A:A,">="&F1)-COUNTIF(A:A,">"&F2)

Regards
Ken........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[geo40]

To all,

Thanks for the support;

Now what if I add two more conditions/ dimensions to the formula:

-Department;
-Own employee or hired personnel;

I want to count if the employment date of a employee is within the given date range, and when the employee is working on a specific department and whether this employee is our own or hired.

Does this issue give problems??

Regards,

GEO40

 

[macropod]

Hi geo40,

Adding extra conditions requires a different approach, either:
. a completely different formula; or
. a different workbook structure.

The former is probably easier to manage. Suppose, for example, the:
. Department data are in column B; and
. Emplyee Types (eg Staff/Contractor) data are in column C.
Suppose also you have a series of one-cell named ranges defining the:
. StartDate
. EndDate
. Department
. EmpType

To find out how many employees of a given type worked in a given Department from the 'StartDate' to the 'EndDate', you could use an array formula like:
{=SUM(IF((StartDate<=$A$1:OFFSET($A$1,COUNTA($A:$A),))*(EndDate>=$A$1:OFFSET($A$1,COUNTA($A:$A),))*(Department=$B$1:OFFSET($B$1,COUNTA($A:$A),))*(EmpType=$C$1:OFFSET($C$1,COUNTA($A:$A),)),1,))}

Cheers
PS: The array braces (ie {}) aren't typed - they're creted by pressing Ctrl-Shift-Enter when you've input the formula.