Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
read data from a cell absolutely | Excel Worksheet Functions | |||
confused about date format | Excel Discussion (Misc queries) | |||
Date programming | Excel Worksheet Functions | |||
Absolutely Stumped! | Excel Discussion (Misc queries) | |||
Date Programming | Excel Programming |