day/month/year in incorrect format for date format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a date format written as "31/1/2005", when I concatenate after I've
separated text to columns I can't change format to 31-Jan-2005.
 
Format as date and add -- in front of "31/1/2005" however if you have US
regional settings you will always get text since there is no month 23 in
that case you need to change the concatenate formula or the regional
settings

--
Regards,

Peo Sjoblom

(No private emails please)
 
Suppose your 3 cells were A1, A2 & A3.
Use
=datevalue(concatenate(A1 & "/" & A2 & "/" & A3))
This turns the text into a date number. It can then be changed with Format -
Cell -Date and the date system you want to use.

Hope this helps
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem formatting dates from CSV file 2
Concatenating Criteria in DSUM 2
IF Dates 1
conditional format, day of month 5
Excel Colour code dates 1
Assign each week number to corresponding month of the year 5
convert day of year to numeric value format year+day in 4 digits 3
(Part 2) - Retrieve Numeric Label for Max Value by Specific Day & Month 4

Back
Top