Jump to content

Welcome to Geeks to Go - Register now for FREE

Need help with your computer or device? Want to learn new tech skills? You're in the right place!
Geeks to Go is a friendly community of tech experts who can solve any problem you have. Just create a free account and post your question. Our volunteers will reply quickly and guide you through the steps. Don't let tech troubles stop you. Join Geeks to Go now and get the support you need!

How it Works Create Account
Photo

Excel date format


  • Please log in to reply

#1
crudler

crudler

    Member

  • Member
  • PipPip
  • 25 posts
Hi

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
BUT
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 ( :) )

Thanks!
  • 0

Advertisements


#2
jasssj

jasssj

    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

#3
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
Easy.

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