0

I have an excel file, with a date column, but I want to convert the date column to

YY/MM/DD/Time

Ive been searching for 2 hours and no result yet.

This is my data:

Source Data: https://i.stack.imgur.com/75zbS.jpg

Expected Output: YY/MM/DD/Time

Can someone help me how I can do it? I want to insert it into postgresql and I want to change everything to compatible date format.

EDIT: I have tried Right Click -> Format cells -> date but it does not change anything!

Thanks

Dark Knight
  • 503
  • 2
  • 12
  • 25

3 Answers3

2

You could use this method and split the date and time into separate cells:

=DATE((LEFT(A1,4)),(MID(A1,5,2)),MID(A1,7,2))

=TIME(MID(A1,10,2),(MID(A1,12,2)),0)

Once your date value is in a format Excel can recognize, you can then change the formatting to whatever you'd like.

Or if you don't care to have the value in a recognizable date format, you can just get your desired formatting like this (will give you a string that looks like this: YY/MM/DD/Time):

=MID(A1,3,2)&"/"&MID(A1,5,2)&"/"&MID(A1,7,2)&"/"&MID(A1,10,4)
dosdel
  • 1,118
  • 8
  • 8
1

ISO 8601 format would be YYYY-MM-DD H24:MI:SS.

But you can set Postgres to accept various date styles by setting the datestyle setting. You can do that globally in postgresql.conf or temporarily for your session.

SET datestyle = SQL, DMY

For more exotic formats, you can create a temporary staging table, COPY to it and INSERT into your target table from there. Among others, you can use to_timestamp():

SELECT to_timestamp('13/10/14/17:33', 'YY/MM/DD/hh24:mi')

More info and example code in related answers like these:
Replacing whitespace with sed in a CSV (to use w/ postgres copy command)
How to bulk insert only new rows in PostreSQL

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • =TEXT(A1,”yyyy-mm-ddThh:MM:ss”) is actual iso format (+/- fractional seconds and timezone). Apparently postgress couldn't be bothered with the T though so =TEXT(A1,”yyyy-mm-dd hh:MM:ss”) – Dirk Bester Mar 18 '15 at 23:48
  • @DirkBester: It's 'YYYY-MM-DD HH24:MI:SS'. Case is not relevant for these parts. 'MM' would be month in Postgres. 'HH' would be 12-hour time in Postgres. And it is It is permitted in ISO 8601 to omit the 'T' character by mutual agreement. – Erwin Brandstetter Mar 19 '15 at 12:22
0

Your going to have to parse the date into four columns using fixed parsing. Then reassemble the columns any way you want. Just Google with excel parse columns fixed.

TimSPQR
  • 2,964
  • 3
  • 20
  • 29