Date programming - Absolutely confused.
 

Hi

I have a spreadsheet with a thousand or so records in it. Column H from row 3 onwards is the date in which particular companies were formed. So, column H holds a thousand plus different dates.

There is nothing in column I yet but what I would like to be able to do is to insert a new date into the cell for each record - BUT, the date shown myust be based on 10 year anniversaries of the formation of each company but only showing the next 10 year anniversary AFTER TODAYS date.

For example, if a date in one of the rows in column H is 25/07/1953, I would like the value added in the cell next to it to be 25/07/2013, not 25/07/1963.

Not all companies were formed in 1953 so the macro or programme will need to be able to differentiate between the years.

Below is an example of how it may start and how I would like it to look after running the programme.

Acme 27/05/1953
Test 13/09/1975
Another 16/10/1968

And after running the macro or program

Acme 27/05/1953 27/05/2013
Test 13/09/1975 13/09/2005
Another 16/10/1968 16/10/2008

As much information on how to achieve this would be greatly appreciated as I am not to familiar with programming.

It is not a problem if a new sheet is inserted when running the program in order to achieve this.

Thanks in advance

Malycom

Date programming - Absolutely confused.
 

Malycom,

For an anniversary date in H1, use the formula below in I1. The formula can
be copied down to match your list of dates.

=IF(DATE(MOD(YEAR(H1),10)+2000,MONTH(H1),DAY(H1)) NOW(),DATE(MOD(YEAR(H1),10
)+2000,MONTH(H1),DAY(H1)),DATE(MOD(YEAR(H1),10)+20 10,MONTH(H1),DAY(H1)))

Watch the extra line breaks inserted by the newsreader programs.

HTH,
Bernie
MS Excel MVP

"Malycom" wrote in message
...
Hi

I have a spreadsheet with a thousand or so records in it. Column H from
row 3 onwards is the date in which particular companies were formed. So,
column H holds a thousand plus different dates.

There is nothing in column I yet but what I would like to be able to do is
to insert a new date into the cell for each record - BUT, the date shown
myust be based on 10 year anniversaries of the formation of each company but
only showing the next 10 year anniversary AFTER TODAYS date.

For example, if a date in one of the rows in column H is 25/07/1953, I
would like the value added in the cell next to it to be 25/07/2013, not
25/07/1963.

Not all companies were formed in 1953 so the macro or programme will need
to be able to differentiate between the years.

Below is an example of how it may start and how I would like it to look
after running the programme.

Acme 27/05/1953
Test 13/09/1975
Another 16/10/1968

And after running the macro or program

Acme 27/05/1953 27/05/2013
Test 13/09/1975 13/09/2005
Another 16/10/1968 16/10/2008

As much information on how to achieve this would be greatly appreciated as
I am not to familiar with programming.

It is not a problem if a new sheet is inserted when running the program in
order to achieve this.

Thanks in advance

Malycom

Date programming - Absolutely confused.
 

Thanks Bernie

This worked a treat.

Can't quite figure out how to put the icon up that indicates that the question is answered so just letting you know the problem is solved.

Regards

Malycom

"Bernie Deitrick" wrote:

Malycom,

For an anniversary date in H1, use the formula below in I1. The formula can
be copied down to match your list of dates.

=IF(DATE(MOD(YEAR(H1),10)+2000,MONTH(H1),DAY(H1)) NOW(),DATE(MOD(YEAR(H1),10
)+2000,MONTH(H1),DAY(H1)),DATE(MOD(YEAR(H1),10)+20 10,MONTH(H1),DAY(H1)))

Watch the extra line breaks inserted by the newsreader programs.

HTH,
Bernie
MS Excel MVP

"Malycom" wrote in message
...
Hi

I have a spreadsheet with a thousand or so records in it. Column H from
row 3 onwards is the date in which particular companies were formed. So,
column H holds a thousand plus different dates.

There is nothing in column I yet but what I would like to be able to do is
to insert a new date into the cell for each record - BUT, the date shown
myust be based on 10 year anniversaries of the formation of each company but
only showing the next 10 year anniversary AFTER TODAYS date.

For example, if a date in one of the rows in column H is 25/07/1953, I
would like the value added in the cell next to it to be 25/07/2013, not
25/07/1963.

Not all companies were formed in 1953 so the macro or programme will need
to be able to differentiate between the years.

Below is an example of how it may start and how I would like it to look
after running the programme.

Acme 27/05/1953
Test 13/09/1975
Another 16/10/1968

And after running the macro or program

Acme 27/05/1953 27/05/2013
Test 13/09/1975 13/09/2005
Another 16/10/1968 16/10/2008

As much information on how to achieve this would be greatly appreciated as
I am not to familiar with programming.

It is not a problem if a new sheet is inserted when running the program in
order to achieve this.

Thanks in advance

Malycom

Date programming - Absolutely confused.
 

AFAIK, there is no icon indicating the question is answered, unless you're
posting through somebody's web interface. Many, if not most, replies are
posted using newsreaders, which don't have such a utility.

Anyway, thanks for letting us know that you were able to work things out -
I knew the problem was solved ;-)

Bernie
MS Excel MVP

"malycom" wrote in message
...
Thanks Bernie

This worked a treat.

Can't quite figure out how to put the icon up that indicates that the
question is answered so just letting you know the problem is solved.

Regards

Malycom

"Bernie Deitrick" wrote:

Malycom,

For an anniversary date in H1, use the formula below in I1. The formula
can
be copied down to match your list of dates.


=IF(DATE(MOD(YEAR(H1),10)+2000,MONTH(H1),DAY(H1)) NOW(),DATE(MOD(YEAR(H1),10
)+2000,MONTH(H1),DAY(H1)),DATE(MOD(YEAR(H1),10)+20 10,MONTH(H1),DAY(H1)))

Watch the extra line breaks inserted by the newsreader programs.

HTH,
Bernie
MS Excel MVP

"Malycom" wrote in message
...
Hi

I have a spreadsheet with a thousand or so records in it. Column H
from
row 3 onwards is the date in which particular companies were formed.
So,
column H holds a thousand plus different dates.

There is nothing in column I yet but what I would like to be able to
do is
to insert a new date into the cell for each record - BUT, the date shown
myust be based on 10 year anniversaries of the formation of each company
but
only showing the next 10 year anniversary AFTER TODAYS date.

For example, if a date in one of the rows in column H is 25/07/1953, I
would like the value added in the cell next to it to be 25/07/2013, not
25/07/1963.

Not all companies were formed in 1953 so the macro or programme will
need
to be able to differentiate between the years.

Below is an example of how it may start and how I would like it to
look
after running the programme.

Acme 27/05/1953
Test 13/09/1975
Another 16/10/1968

And after running the macro or program

Acme 27/05/1953 27/05/2013
Test 13/09/1975 13/09/2005
Another 16/10/1968 16/10/2008

As much information on how to achieve this would be greatly
appreciated as
I am not to familiar with programming.

It is not a problem if a new sheet is inserted when running the
program in
order to achieve this.

Thanks in advance

Malycom