Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to formulate this easily??? | Excel Worksheet Functions | |||
how to sum this easily...? | Excel Worksheet Functions | |||
anyway around this, easily? | Excel Worksheet Functions | |||
how to count easily | Excel Worksheet Functions | |||
Can this be done easily? | Excel Discussion (Misc queries) |