names to numbers

[afgi]

Hi,

I have problem with change of names of the months to numbers. however, this is just a part of what I am doing. I will go with whole process step by step, pls also see the excel table attached:

1. I one of the columns I have this kind text "2010/jul/29". this is not being seen as date by excel, but as text. each cell has different data
2. so, to make excel see it as a text, I need to break it into its constituent and then use the formula Date
3. this formula has 3 subcategories: year, month, day. and excel needs numbers, not text to make it work. so the problem occures in month part. that is "jul" (or any other, like "jun", "sep"
4. I need a macro or update formula, that could change the text to number, and I need to add it to my Data formula.
5. I have placed the ready formula in the excel table attached. I do not paste it here, because my formula is not in english, but your excel will read it in your language. date/text is in A1, formula is in C1
   
   thanks for your help in creating of new formula or macro.

[Andrew-R]

If your data is always in *exactly* the same format then this formula will convert it to a real date...

Please Login or Register  to view this content.

If the day of the month is sometimes one digit and sometimes two then that will require a little tweak.

[Andrew-R]

PS - that formula is based on your data being in cell A2, if that wasn't clear.

[JBeaucaire]

All the parameters in a DATE() formula have to be numeric, but you've used text functions to pull out the pieces. Each piece needs to be converted back into a number, like so:

=DATE(LEFT(A1,4)+0, MONTH(MID(A1, FIND("/", A1)+1, 3)&1), RIGHT(A1,2)+0)

[snb]

Why do you use '2010/jul/29' instead of '29/jul/2010' ?

[afgi]

Hi,

this is date downloaded from web page, sequence is the result of page administartors. reg formulas, may I ask you to always attached excel file with your formulas. I have excel in polish, so sometimes it is hard for me to find counterparts of your english formulas. I am not so fluent in excel

[Marcol]

And another way....

This will convert your string to an actual date.

Please Login or Register  to view this content.

You can then format the cells:= yyyy/mmm/dd

Hope this helps

[snb]

or

=DATEVALUE(RIGHT(A1;2) & "-" & MID(A1;6;LEN(A1)-8) & "-" & LEFT(A1;4))

[snb]

or with VBA:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[afgi]

Hi,

do you know maybe how to create User define function that combines code

Please Login or Register  to view this content.

and function that has been created in this thread? pls see those in the excel file attached. column A is the text I have at the begining. column C is a result of above User Defined Function. Column G is function that changes text to proper date format. So I need to comnbine this process into one User Defined Function or a macro. many thanks

[JBeaucaire]

FYI, my formula works as entered on the spreadsheet above. Simply select the cell I2, press F2 and ENTER to reassert the formula.

[afgi]

Dear Jerry,

I need it to extract information directly from column A f.e. A2. and I want to have a User Defined Formula to be made. I do not know how to creat one.
I need it to work on many workbooks, that is why i do not wont to copy& paste this formula each time I work on different files. many thanks

[snb]

Please Login or Register  to view this content.

put into cell B1:

=date_01(A1)

[afgi]

Dear SNB,

does not work

[snb]

You are right.

therefore you'd better not use a function but the macro I gave you earlier: for instance the dates are in column A:

the only code you need is:

Please Login or Register  to view this content.

[Marcol]

I'm a bit lost in this thread, but in reply to your PM

Please Login or Register  to view this content.

This will cover for the possibility of blanks and "nearly" dates.

See attachment for all the solutions offered compared

[afgi]

works great