Calculating Anniversary

Help! I have to calculate how many years each of our employees have been
here as of December 31, 2005.

I have all of the information in a spreadsheet, the only thing I have to
base it off of is their hire date which is in date format mm/dd/yyyy. If
they have not been here a year, I would like it to either null out or put in
"0" or some other bogus character so I don't include them.

Any suggestions would be greatly appreciated. Thanks so much.

=IF(AND(MONTH(TODAY())>=MONTH(A1),DAY(TODAY())>=DAY(A1)),YEAR(TODAY())-YEAR(A1),YEAR(TODAY())-YEAR(A1)-1)

Where A1 is the Hire Date. This just says if the current month and day is
greater than the hire date month and day, then subtract the years of the two
dates. Otherwise subtract the years of the two dates and subtract 1. This
will output "0" if it is less than a year. If you want it to output "null"
isntead of "0" you can do that with a custom format of type...

#;#;"null"

Hope this helps.

"Penny" wrote:

> Help! I have to calculate how many years each of our employees have been
> here as of December 31, 2005.
>
> I have all of the information in a spreadsheet, the only thing I have to
> base it off of is their hire date which is in date format mm/dd/yyyy. If
> they have not been here a year, I would like it to either null out or put in
> "0" or some other bogus character so I don't include them.
>
> Any suggestions would be greatly appreciated. Thanks so much.

Will this calculate it based on 12/31/05?

"Sloth" wrote:

> =IF(AND(MONTH(TODAY())>=MONTH(A1),DAY(TODAY())>=DAY(A1)),YEAR(TODAY())-YEAR(A1),YEAR(TODAY())-YEAR(A1)-1)
>
> Where A1 is the Hire Date. This just says if the current month and day is
> greater than the hire date month and day, then subtract the years of the two
> dates. Otherwise subtract the years of the two dates and subtract 1. This
> will output "0" if it is less than a year. If you want it to output "null"
> isntead of "0" you can do that with a custom format of type...
>
> #;#;"null"
>
> Hope this helps.
>
> "Penny" wrote:
>
> > Help! I have to calculate how many years each of our employees have been
> > here as of December 31, 2005.
> >
> > I have all of the information in a spreadsheet, the only thing I have to
> > base it off of is their hire date which is in date format mm/dd/yyyy. If
> > they have not been here a year, I would like it to either null out or put in
> > "0" or some other bogus character so I don't include them.
> >
> > Any suggestions would be greatly appreciated. Thanks so much.

If you want to compare the hire date to 12/31/05 rather than today's date,
change "TODAY()" to "DATE(2005,12,31)" in the provided formula.

--
Regards,
Fred


"Penny" <Penny@discussions.microsoft.com> wrote in message
news:6A902B42-554B-4554-A12F-31B02B956BEB@microsoft.com...
> Will this calculate it based on 12/31/05?
>
> "Sloth" wrote:
>
>> =IF(AND(MONTH(TODAY())>=MONTH(A1),DAY(TODAY())>=DAY(A1)),YEAR(TODAY())-YEAR(A1),YEAR(TODAY())-YEAR(A1)-1)
>>
>> Where A1 is the Hire Date. This just says if the current month and day is
>> greater than the hire date month and day, then subtract the years of the two
>> dates. Otherwise subtract the years of the two dates and subtract 1. This
>> will output "0" if it is less than a year. If you want it to output "null"
>> isntead of "0" you can do that with a custom format of type...
>>
>> #;#;"null"
>>
>> Hope this helps.
>>
>> "Penny" wrote:
>>
>> > Help! I have to calculate how many years each of our employees have been
>> > here as of December 31, 2005.
>> >
>> > I have all of the information in a spreadsheet, the only thing I have to
>> > base it off of is their hire date which is in date format mm/dd/yyyy. If
>> > they have not been here a year, I would like it to either null out or put
>> > in
>> > "0" or some other bogus character so I don't include them.
>> >
>> > Any suggestions would be greatly appreciated. Thanks so much.

I did it like this:
=IF(AND(MONTH(DATE(2005,12,31))>=MONTH(A1),DAY(DATE(2005,12,31))>=DAY(A1)),YEAR(DATE(2005,12,31))-YEAR(A1),YEAR(DATE(2005,12,31))-YEAR(A1)-1)

and it said there was an error...it just showed up as #VALUE!

"Fred Smith" wrote:

> If you want to compare the hire date to 12/31/05 rather than today's date,
> change "TODAY()" to "DATE(2005,12,31)" in the provided formula.
>
> --
> Regards,
> Fred
>
>
> "Penny" <Penny@discussions.microsoft.com> wrote in message
> news:6A902B42-554B-4554-A12F-31B02B956BEB@microsoft.com...
> > Will this calculate it based on 12/31/05?
> >
> > "Sloth" wrote:
> >
> >> =IF(AND(MONTH(TODAY())>=MONTH(A1),DAY(TODAY())>=DAY(A1)),YEAR(TODAY())-YEAR(A1),YEAR(TODAY())-YEAR(A1)-1)
> >>
> >> Where A1 is the Hire Date. This just says if the current month and day is
> >> greater than the hire date month and day, then subtract the years of the two
> >> dates. Otherwise subtract the years of the two dates and subtract 1. This
> >> will output "0" if it is less than a year. If you want it to output "null"
> >> isntead of "0" you can do that with a custom format of type...
> >>
> >> #;#;"null"
> >>
> >> Hope this helps.
> >>
> >> "Penny" wrote:
> >>
> >> > Help! I have to calculate how many years each of our employees have been
> >> > here as of December 31, 2005.
> >> >
> >> > I have all of the information in a spreadsheet, the only thing I have to
> >> > base it off of is their hire date which is in date format mm/dd/yyyy. If
> >> > they have not been here a year, I would like it to either null out or put
> >> > in
> >> > "0" or some other bogus character so I don't include them.
> >> >
> >> > Any suggestions would be greatly appreciated. Thanks so much.
>
>
>

I AM SO STUPID!! DUH! The first date I had entered was in cell A2, not A1.
What a rookie. Thanks so much for the help, it took me 2 seconds and
normally takes me 4 days to figure it out by hand. Thank you, thank you,
thank you!




"Penny" wrote:

> I did it like this:
> =IF(AND(MONTH(DATE(2005,12,31))>=MONTH(A1),DAY(DATE(2005,12,31))>=DAY(A1)),YEAR(DATE(2005,12,31))-YEAR(A1),YEAR(DATE(2005,12,31))-YEAR(A1)-1)
>
> and it said there was an error...it just showed up as #VALUE!
>
> "Fred Smith" wrote:
>
> > If you want to compare the hire date to 12/31/05 rather than today's date,
> > change "TODAY()" to "DATE(2005,12,31)" in the provided formula.
> >
> > --
> > Regards,
> > Fred
> >
> >
> > "Penny" <Penny@discussions.microsoft.com> wrote in message
> > news:6A902B42-554B-4554-A12F-31B02B956BEB@microsoft.com...
> > > Will this calculate it based on 12/31/05?
> > >
> > > "Sloth" wrote:
> > >
> > >> =IF(AND(MONTH(TODAY())>=MONTH(A1),DAY(TODAY())>=DAY(A1)),YEAR(TODAY())-YEAR(A1),YEAR(TODAY())-YEAR(A1)-1)
> > >>
> > >> Where A1 is the Hire Date. This just says if the current month and day is
> > >> greater than the hire date month and day, then subtract the years of the two
> > >> dates. Otherwise subtract the years of the two dates and subtract 1. This
> > >> will output "0" if it is less than a year. If you want it to output "null"
> > >> isntead of "0" you can do that with a custom format of type...
> > >>
> > >> #;#;"null"
> > >>
> > >> Hope this helps.
> > >>
> > >> "Penny" wrote:
> > >>
> > >> > Help! I have to calculate how many years each of our employees have been
> > >> > here as of December 31, 2005.
> > >> >
> > >> > I have all of the information in a spreadsheet, the only thing I have to
> > >> > base it off of is their hire date which is in date format mm/dd/yyyy. If
> > >> > they have not been here a year, I would like it to either null out or put
> > >> > in
> > >> > "0" or some other bogus character so I don't include them.
> > >> >
> > >> > Any suggestions would be greatly appreciated. Thanks so much.
> >
> >
> >