I am trying to track perfect attendance which is defined as a full year with
no absences. I need to be able to distinguish between employees who acheive
this for 1 year, 2 years, 3 years and so on.

The data that we use is currently set up like this:

A2: Employee Name
B2: 2nd to last most recent date absent
C2: Most recent date absent

I need to run a formula that will tell me not only if there is a year or
more between the dates in B & C, but also if there is currently or was a year
or more difference in the date in A4 and any date in the current month.

So tomorrow (4.1.08), I would run a report that gives me all absences for
March 2008 and plug those into column C (and delete & move down the last most
recent date to column B). If any employee WAS absent in March but they still
had a year between that and thier previous most recent absence, I need to
know to give them an award, but also if they DID NOT have any absences in
March, but thier most recent date in A4 was in March of a previous year,
earning them the award for either 1, 2, 3, ect years.

Any ideas on what the appropriate formula will be? Thanks in advance for
the help. You all are awesome.

You will probably want to make use of the DATEDIF function (don't look, it
is not in your help files unless you are using XL2000). See this link...

http://www.cpearson.com/excel/datedif.aspx

Rick


"hmsawyer" wrote in message
...
I am trying to track perfect attendance which is defined as a full year
with
no absences. I need to be able to distinguish between employees who
acheive
this for 1 year, 2 years, 3 years and so on.

The data that we use is currently set up like this:

A2: Employee Name
B2: 2nd to last most recent date absent
C2: Most recent date absent

I need to run a formula that will tell me not only if there is a year or
more between the dates in B & C, but also if there is currently or was a
year
or more difference in the date in A4 and any date in the current month.

So tomorrow (4.1.08), I would run a report that gives me all absences for
March 2008 and plug those into column C (and delete & move down the last
most
recent date to column B). If any employee WAS absent in March but they
still
had a year between that and thier previous most recent absence, I need to
know to give them an award, but also if they DID NOT have any absences in
March, but thier most recent date in A4 was in March of a previous year,
earning them the award for either 1, 2, 3, ect years.

Any ideas on what the appropriate formula will be? Thanks in advance for
the help. You all are awesome.

Rick,

That's very interesting. Are there any more undocumented functions?

Bill Ridgeway
Computer Solutions

"Rick Rothstein (MVP - VB)" wrote in
message ...
You will probably want to make use of the DATEDIF function (don't look, it
is not in your help files unless you are using XL2000). See this link...

http://www.cpearson.com/excel/datedif.aspx

Rick


"hmsawyer" wrote in message
...
I am trying to track perfect attendance which is defined as a full year
with
no absences. I need to be able to distinguish between employees who
acheive
this for 1 year, 2 years, 3 years and so on.

The data that we use is currently set up like this:

A2: Employee Name
B2: 2nd to last most recent date absent
C2: Most recent date absent

I need to run a formula that will tell me not only if there is a year or
more between the dates in B & C, but also if there is currently or was a
year
or more difference in the date in A4 and any date in the current month.

So tomorrow (4.1.08), I would run a report that gives me all absences for
March 2008 and plug those into column C (and delete & move down the last
most
recent date to column B). If any employee WAS absent in March but they
still
had a year between that and thier previous most recent absence, I need to
know to give them an award, but also if they DID NOT have any absences in
March, but thier most recent date in A4 was in March of a previous year,
earning them the award for either 1, 2, 3, ect years.

Any ideas on what the appropriate formula will be? Thanks in advance for
the help. You all are awesome.