Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Penny
 
Posts: n/a
Default 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.
  #2   Report Post  
Sloth
 
Posts: n/a
Default

=IF(AND(MONTH(TODAY())=MONTH(A1),DAY(TODAY())=DA Y(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.

  #3   Report Post  
Penny
 
Posts: n/a
Default

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

"Sloth" wrote:

=IF(AND(MONTH(TODAY())=MONTH(A1),DAY(TODAY())=DA Y(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.

  #4   Report Post  
Fred Smith
 
Posts: n/a
Default

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" wrote in message
...
Will this calculate it based on 12/31/05?

"Sloth" wrote:

=IF(AND(MONTH(TODAY())=MONTH(A1),DAY(TODAY())=DA Y(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.



  #5   Report Post  
Penny
 
Posts: n/a
Default

I did it like this:
=IF(AND(MONTH(DATE(2005,12,31))=MONTH(A1),DAY(DAT E(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" wrote in message
...
Will this calculate it based on 12/31/05?

"Sloth" wrote:

=IF(AND(MONTH(TODAY())=MONTH(A1),DAY(TODAY())=DA Y(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.






  #6   Report Post  
Penny
 
Posts: n/a
Default

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(DAT E(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" wrote in message
...
Will this calculate it based on 12/31/05?

"Sloth" wrote:

=IF(AND(MONTH(TODAY())=MONTH(A1),DAY(TODAY())=DA Y(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.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sprdsht not calculating [email protected] Excel Discussion (Misc queries) 3 July 25th 05 07:12 PM
calculate anniversary of date after specified date slymeat Excel Worksheet Functions 3 July 5th 05 01:53 AM
Calculating recurring date in following month, calculating # days in that period Walterius Excel Worksheet Functions 6 June 4th 05 11:21 PM
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) RICHARD Excel Worksheet Functions 1 March 15th 05 06:49 PM
AGE CALCULATING EXCEL SPREADSHEETS AND OTHERS! RICHARD Excel Discussion (Misc queries) 0 March 1st 05 02:53 PM


All times are GMT +1. The time now is 12:03 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"