put your cursor over the date and do the following -
Category - Custom
Thats should do you.
I have the following problem,
I have a list of dates with the following format (in the string):
"07 July 2004"
I want to transfer this into a date (07.07.2004) but I have no idea how to transfer the extracted month name (July in this example) into a number (07).
Tnx a lot in advance.
if that was the case I would not make this post... The name of the month is a text string and is extracted as such - "june" first. Second, the cell contains as I specified spaces between the date, the month and the year that's why Excel could not recognise it as a date. The one way I could do this is to make another sheet with the names of the months and the number of each month in the neighbour cell, and then link the text string to the number, but I guess that there may be a formula transferring the text string "june" into 6, which was my question.
You can run up the "Data > Text To Columns" command.
1. Select your dates
2. Click Data > Text to Columns
3. Select the "Delimited" option in step 1 of the Wizard and click Next
4. Click Next again
5. In step 3 of the wizard, select "Date" under Column Data format section, with the DMY style
6. Click Finish
A procedure or a function should do it.
Dim date1 as integer, date2 as integer, date3 as integer, temp
date1 = left(yourCell,2)
date3 = right(yourcell,4)
temp = left(yourcell, len(yourCell)-4)
temp = right(temp, len(temp - 2))
temp = trim(temp)
yourCell = date1 & "." & date2 & "." & date3
You might have to mess around with the formatting to get it to show as you want it to.
How about using:
(assuming the string is in A1).
My Excel Web Page
"You're only given a little spark of madness. You mustn't lose it." - Robin Williams
I tried the Text-To-Columns command for a range of dates (with and without quote marks). It worked fine.
Perhaps I haven't correctly understood what your requirement is. It appears from your post that you would like a date such as:
"07 July 2004"
To appear as:
If this is what you want, then Text-To-Columns will convert a text entry into a TRUE date. The next step would be to apply the date format of your choice ("dd.mm.yyyy").
There are currently 1 users browsing this thread. (0 members and 1 guests)