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" <(E-Mail Removed)> wrote in message
news:051F584E-0FEA-4D25-9AB3-(E-Mail Removed)...
>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)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:051F584E-0FEA-4D25-9AB3-(E-Mail Removed)...
>>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.
>