• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help in converting text to date with specific date_time format

vk7

Member
I would like to convert the dateformat in the attached excel sheet to YYYY-MM-DD HH:MM:SS

what is the quickest way? can this be achieved using a formula?
 

Attachments

  • Date.xlsx
    9.4 KB · Views: 7
Last edited:
Select A1:A16 >> Custom Cell Format, enter : yyyy-mm-dd hh:mm:ss >> OK

then,

>> Press Ctrl+H >>
  • Find what : " . "
  • Replace with : " - "
  • Press "Replace All"
  • Finish
 
Last edited:
  • Like
Reactions: vk7
Hello @bosco_yip, I have tried that already but the below step doesn't seem to have any effect.. I hope it should change the format of the date to (2020-10-21 12:20:00) instead it still shows (21-10-2020 12:20). Attached the sheet for your reference.

Select A1:A16 >> Custom Cell Format, enter : yyyy-mm-dd hh:mm:ss >> OK
 

Attachments

  • Date.xlsx
    9.5 KB · Views: 5
Hello @bosco_yip, I have tried that already but the below step doesn't seem to have any effect.. I hope it should change the format of the date to (2020-10-21 12:20:00) instead it still shows (21-10-2020 12:20). Attached the sheet for your reference.

Select A1:A16 >> Custom Cell Format, enter : yyyy-mm-dd hh:mm:ss >> OK
In your 2nd file, change your custom cell format from yyyy/mm/dd hh:mm:ss to yyyy-mm-dd hh:mm:ss

Then, select A1: A16 again >> Text to Column >> click "Finish"

or,

In the above post #2 please see the new edited.
 
  • Like
Reactions: vk7
@vk7

What @bosco_yip is telling you is that your column is text string and not proper date time value.
By using method explained by bosco_yip, Text to Columns will convert string representation of datetime to actual date time value.

However, doing so will apply specific formatting to datetime value. So by applying custom format "after" text to column operation. It will return desired result.
 
Since I use Microsoft 365 the formula solution could be
Code:
= LET(
  datetime, SUBSTITUTE(DateText, ".","-"),
  date, DATEVALUE(datetime),
  time, TIMEVALUE(datetime),
  date+time )
where DateText is the entire column of dates. The formula gives a combined datetime that I formatted
yyyy-mm-dd* hh:mm:ss
I like the "* " because it justifies the date to the left and the date to the right.

An alternative is to separate the date and time into two columns.
Code:
= LET(
  datetime, SUBSTITUTE(DateText, ".","-"),
  date, DATEVALUE(datetime),
  time, TIMEVALUE(datetime),
  IF({1,0}, date, time) )
 
  • Like
Reactions: vk7
Back
Top