help to count easily
 

hello,

i have 100 records of different employees. running for 3 years of weekly
payroll enlistment. Some are intermittently employed for multiple times yet
are considered under one (1) ID No.

in my spreadsheet for all employees, as one example of one employee, it
looks like this, columns A:D

A B C D E F
ID NO NAME P_START P_END COUNT MONTH
SC-001 MARIA 1-Jun-07 7-Jun-07 1 Jun-07
SC-001 MARIA 11-May-07 17-May-07 - -
SC-001 MARIA 4-May-07 10-May-07 1 May-07
SC-001 MARIA 27-Apr-07 3-May-07 - -
SC-001 MARIA 6-Apr-07 12-Apr-07 1 Apr-07
SC-001 MARIA 30-Mar-07 5-Apr-07 1 Mar-07
SC-001 MARIA 18-Dec-05 24-Dec-05 - -
SC-001 MARIA 11-Dec-05 17-Dec-05 - -
SC-001 MARIA 4-Dec-05 10-Dec-05 1 Dec-05
SC-001 MARIA 27-Nov-05 3-Dec-05 - -
SC-001 MARIA 20-Nov-05 26-Nov-05 1 Nov-05
---------- ---------- ---------- ---------- ---------- ----------
SC-012 MARIA total months enlisted 6

Actually, data on Columns E and F, as shown above, do not exist in the
spreadsheet, i just want to show how to count the "total months enlisted".
Can someone help me to make a formula for column E and F so i can easily
include it in my pivot table currently covering columns A:D only, as an
example.

The count of *calendar* months enlisted, per employee ID will be used as
reference for many aspects of my labour cost summary calculation.

thanks and regards,
driller
--
*****
birds of the same feather flock together..

help to count easily
 

=DATEDIF(C2,D2,"m")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"driller" wrote in message
...
hello,

i have 100 records of different employees. running for 3 years of weekly
payroll enlistment. Some are intermittently employed for multiple times
yet
are considered under one (1) ID No.

in my spreadsheet for all employees, as one example of one employee, it
looks like this, columns A:D

A B C D E F
ID NO NAME P_START P_END COUNT MONTH
SC-001 MARIA 1-Jun-07 7-Jun-07 1 Jun-07
SC-001 MARIA 11-May-07 17-May-07 - -
SC-001 MARIA 4-May-07 10-May-07 1 May-07
SC-001 MARIA 27-Apr-07 3-May-07 - -
SC-001 MARIA 6-Apr-07 12-Apr-07 1 Apr-07
SC-001 MARIA 30-Mar-07 5-Apr-07 1 Mar-07
SC-001 MARIA 18-Dec-05 24-Dec-05 - -
SC-001 MARIA 11-Dec-05 17-Dec-05 - -
SC-001 MARIA 4-Dec-05 10-Dec-05 1 Dec-05
SC-001 MARIA 27-Nov-05 3-Dec-05 - -
SC-001 MARIA 20-Nov-05 26-Nov-05 1 Nov-05
---------- ---------- ---------- ---------- ---------- ----------
SC-012 MARIA total months enlisted 6

Actually, data on Columns E and F, as shown above, do not exist in the
spreadsheet, i just want to show how to count the "total months enlisted".
Can someone help me to make a formula for column E and F so i can easily
include it in my pivot table currently covering columns A:D only, as an
example.

The count of *calendar* months enlisted, per employee ID will be used as
reference for many aspects of my labour cost summary calculation.

thanks and regards,
driller
--
*****
birds of the same feather flock together..

help to count easily
 

This works on your sample data but I have a suspicion that there's more to
it!

Count:

=IF(AND(ISNUMBER(C2),C3=""),1,IF(MONTH(C2)<MONTH( C3),1,""))

Month:

=IF(E2=1,DATE(YEAR(C2),MONTH(C2),1),"")

Or:

=IF(E2=1,C2,"")

Formatted as DATE style of your choice.

Biff

"driller" wrote in message
...
hello,

i have 100 records of different employees. running for 3 years of weekly
payroll enlistment. Some are intermittently employed for multiple times
yet
are considered under one (1) ID No.

in my spreadsheet for all employees, as one example of one employee, it
looks like this, columns A:D

A B C D E F
ID NO NAME P_START P_END COUNT MONTH
SC-001 MARIA 1-Jun-07 7-Jun-07 1 Jun-07
SC-001 MARIA 11-May-07 17-May-07 - -
SC-001 MARIA 4-May-07 10-May-07 1 May-07
SC-001 MARIA 27-Apr-07 3-May-07 - -
SC-001 MARIA 6-Apr-07 12-Apr-07 1 Apr-07
SC-001 MARIA 30-Mar-07 5-Apr-07 1 Mar-07
SC-001 MARIA 18-Dec-05 24-Dec-05 - -
SC-001 MARIA 11-Dec-05 17-Dec-05 - -
SC-001 MARIA 4-Dec-05 10-Dec-05 1 Dec-05
SC-001 MARIA 27-Nov-05 3-Dec-05 - -
SC-001 MARIA 20-Nov-05 26-Nov-05 1 Nov-05
---------- ---------- ---------- ---------- ---------- ----------
SC-012 MARIA total months enlisted 6

Actually, data on Columns E and F, as shown above, do not exist in the
spreadsheet, i just want to show how to count the "total months enlisted".
Can someone help me to make a formula for column E and F so i can easily
include it in my pivot table currently covering columns A:D only, as an
example.

The count of *calendar* months enlisted, per employee ID will be used as
reference for many aspects of my labour cost summary calculation.

thanks and regards,
driller
--
*****
birds of the same feather flock together..