• 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

Last edited:

bosco_yip

Excel Ninja
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

vk7

Member
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

bosco_yip

Excel Ninja
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

Chihiro

Excel Ninja
@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.
 

Peter Bartholomew

Well-Known Member
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
Top