Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works

Excel date format

  • Please log in to reply




  • Member
  • PipPip
  • 25 posts

Can someone pls assist me with this.
I get data in csv format from an external source.
The problem is that it gives me dates in a lousy format of ddmmyy

For example 120706 or 050606.

Excel doesn't recognise them as dates and just displays them as numbers (120706 or 50606)

I want to see them as dates, so I select the column and change the format to date
excel sees my "numbers" as a julian date, and jumps me forward to the dates 24/06/2230 and 20/07/2038 respectively.

How can I get around this?
I cannot get the data in a better format as it comes from a mainframe ( :) )

  • 0




    New Member

  • Member
  • Pip
  • 1 posts
I believe you can turn the date into text, and then format it as a date. What I mean by this, to insert the date format if you have it in your version of excel, if not, you can create it the hard way, by using the left mid and right and then use concatenate to put it all together. It works and if you have a fair amount of data it's faster than entering it by hand. I hope this helps, should you have questions on how to use these functions, use the help button it works well to figure out what functions do.
  • 0




  • Member
  • PipPipPip
  • 335 posts

Once the dates are in your spreadsheet, in the '120706' format, do the following.

(Assuming your dates start in cell A1)

1 - in column 'B', insert a new blank column

2 - in cell B1, type the following formula: =DATE(RIGHT(A1,2)+2000,MID(A1,3,2),LEFT(A1,2))

3 - copy this formula down to encompass all your dates

4 - copy and paste the new list AS VALUES into column A

5 - Delete column B.

6 - you might need to format column A afterwards, depends on which version of Excel you're running

You could write a very simple macro to automate this entire process.

Hope this helps!!!
  • 0

Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP