replace ddmmyy in all cells of a column with current day

[the_observer]

Hi all.


I have a column with cells which have data which consists of filename+ddmmyy like this for example : filenameddmmyy or another cell in this row can be sometextddmmyysometext etc


Is there a way i can replace the ddmmyy only for the cells of this column with current day keeping the rest of the text in the cells as is?


For example lets say a cell is sometextddmmyysometext is it possible that if i open the excel to become sometext170513sometext where 170513 is the current day/month/year?

And the next day when i open the excel to become sometext180513sometext e.t.c. only for the cells of a specific column?


Thank you for reading this.


Regards,

the_observer.

 

[catpol123]

See this example:


in cell A1 enter:

aaaddmmyybbb


in cell B1 enter this formula:

=REPLACE(A1,SEARCH("ddmmyy",A1,1),6,TEXT(TODAY(),"ddmmyy"))


If your workbook automatically recalculates when you open it this should always give you the current date as ddmmyy.


This is what I get as a result of this formula: aaa170513bbb


Cheers!

 

[NARAYANK991]

Hi ,


I have a question or two :


1. Will all the dates in the column be identical ? For instance , if the ddmmyyyy is 170513 , will all instances of ddmmyyyy in the column be 170513 ?


2. Will the dates always be referring to the previous day ? For instance , if today is 170513 , will all instances of ddmmyy in the column be 160513 ? Will it happen that you do not open the file for several days , and the dates are all a week or two old ?


Narayan

 

[the_observer]

First of all i would like to thank you for your answers.

Also ,since i am newbie in excel forgive me if i ask something stupid.


@--- > catpol123

i tried what you suggested and it seems to work. New questions now arise for improvement but the original question can be taken as resolved.

The new questions are:

1. So i will have to have another column and each cell will correspond to the newly created cell which have the formulas. Can this be done without adding a new column with the formula and for example this be added in the original column? e.g. textddmmyytext to become text(formula)text.

2. and/or Can this be done with only one cell and the formula to make the substitutions for all the cells of the column and not only A1 cell?


@--- > NARAYANK991

1.No,not all the dates in the column will be identical. There are some cells that have ddmmyy , some cells yyyymmdd and some cells with no date at all.If i will use the way of catpol123 above i guess i will have to modify the formula accordingly for the cells that have yyyymmdd and omit it for the cells that do not have dates.

2.The dates are not referring to the previous day.All the dates are the current day.I open the excel file every day except weekends but every time i open the excel the dates have to be the current day.


Thank you for your time.


Regards,

the_observer.

 

[catpol123]

Re. your point 1.

I think that trying to have a formula in a cell that acts on the very same cell always results in a circular reference error so I think you will need another column.


Re. your point 2.

I am not sure if I understand your question.

Once you have the formula in cell B1 you can just drag the corner of cell B1 down and it will fill in the formulas down the column replacing A1 with A2, A3, A4 etc...for each row.

 

[NARAYANK991]

Hi ,


I am also somewhat confused by your posts ; do you open a new file everyday , or is it the same file with new data ?


When you mention sometextddmmyysometext , do you mean that the portion ddmmyy will be in that manner , or will it be a valid date , such as 170513 ? What I mean is , when you open the file tomorrow , will the column contain data such as :


sometext170513sometext


which is to be changed to


sometext180513sometext


or will it again contain


sometextddmmyysometext


which is to be changed to


sometext180513sometext


Things would be clearer if you could upload a sample workbook with some input data , as well as output data after the transformation.


Narayan

 

[the_observer]

Hi again.


@ --> catpol123

I see.

Thank you very much for your answers


@ -- >NARAYANK991

- I open the same file everyday.

- What i want is the 2nd version of your question like this :


''or will it again contain


sometextddmmyysometext


which is to be changed to


sometext180513sometext ''


I will be able to provide example after Monday as i have not access at the file right now.

Nevertheless , catpol123 answers above are quite satisfactory and the issue can be taken as resolved.


Thank you both for your time.


Regards,

the_observer.