Format of Date

[ALEZI]

Hi,I have searched trough the threads but i didn't find any solution for my problem,i will describe above:

In my Excel table i have in column format of date in next form:21.04.2013,can I change this format with:April-16,where 16 is number of week in 2013.


I hope that is very easy for you,thank you for visit my post.

Aleksandar

[Dunc3142]

You could put this formula in an adjacent cell and it will get you your results.

Please Login or Register  to view this content.

You then could copy the results and paste special, values if needed.

[FDibbins]

Hi

Assuming your date is in A1, and that the date IS actually a date, and not text that looks like a date, try this...

=TEXT(A1,"mmmm")&" "&WEEKNUM(A1,2)

[ALEZI]

Thank guys for quick response but this do not do what i want,i will try again to explain,i have one document from the start this year (2013) i want to retroactive to change format of my data,now format is "24.04.2013" in whole column,can i change this with "April-16" and i have to came beck to start of the year,can we make some formula which is change weeks of the year according to format in the column..

Maybe i was a little confused in my explanation,thank you again for your effort. .

[ALEZI]

Maybe i have to rearrange my question,can i somehow to change my text word "01.05.2013" into some of official formats for date into Excel,can i avoid to change this word cell by cell..

Thank you for watching my post.

[Fotis1991]

Aleksandar .Try using TEXT to COLUMNS option.

[ALEZI]

Thank you Fotis,but i do not understand you,can you explain me again ,i have one big tables where is the date formulated as text "01.05.2013" ,i want to change this by for example:"May-number of the week" and i have to do this the previous reports,i have to go back and change this.
Can i change this by some formula?

Thank you again.

[Fotis1991]

The TEXT to Columns option that i suggested will convert your text to a real date. See how you can use this, in this link.

http://www.myonlinetraininghub.com/e...t-date-formats

But as your final result you want to be April 18, we'll use a SUBSTITUTE inside of FDibbins formula like this.

=TEXT(DATEVALUE(SUBSTITUTE(A1;".";"/"));"mmmm")&" "&WEEKNUM(DATEVALUE(SUBSTITUTE(A1;".";"/"));2)

See the sample.

[ALEZI]

Thank you Fotis,the formula working very well but i have problem because we using here in Serbia format of date as dd.mm.yyyy,formula working when format is mm.dd.yyyy,

Can you somehow change this,is reason for this function DATEVALUE?

Thank you a lot .

[Fotis1991]

You are welcome and thanks for the reb*.

Hoping that your next reply will not be 8 days later, i have to say that i tested the formula and works fine in any format.

dd.mm.yyyy, is a format that works fine.

If you have any issues,pls upload a small sample workbook to see what you mean.

[ALEZI]

Thank you Fotis,you help me a lot..

The formula works fine when we have format of date mm.dd.yyyy,in my case i have dd.mm.yyyy formula shows "Value". How i can change this format because i have to go back years ago in my table.

Thank you again..

[Fotis1991]

I believe that it must be something depanding of the local settings,because:

=TEXT(DATEVALUE(SUBSTITUTE(A2;".";"/"));"μμμμ")&" "&WEEKNUM(DATEVALUE(SUBSTITUTE(A2;".";"/"));2)

This is my formula. As I said in my previous reply, "μμμμ" in Greek, is "mmmm"--Months in English.
In columnC, I translate the results that I get from Greek to English

Also i post a photo of what i see when i use this formula.

[Bhuvi]

Hi Alezi,

I can suggest one probable solution. In the attached sheet I have used a formula in column C which converts the format dd.mm.yyyy into the excel usable format (mm/dd/yyyy) and then finally gives the output as mmm-weeknum.
Please check and let us know if this helps.

Best Regards,
Bhuvanesh

[ALEZI]

Finally,this formula works fine,thank you a lot Bhuvanesh

[Bhuvi]

you are always welcome.






if my answer helped, kindly click on the star* below to add to my rep.