Excel date, time, time zone extraction

Question

Excel date, time, time zone extraction

Curious 0

I'm currently working with a several Excel sheets that have a column with mm/dd/yyyy hh:mm:ss MDT (time zone where it was created). Is there a way or formula to make the current column or a new column with just mm/dd/yyyy? I've tried selecting the column then selecting format & trying different date formats but nothing has happened at all to the data.

Barry Schwarz 3,746 Reputation points

2024-02-02T21:31:21.3733333+00:00

Is the value in the cell text or an Excel date-time value? Select one of the cells that display this and tell us what format is being used for that cell.
Curious 0 Reputation points

2024-02-03T02:09:16.31+00:00

It's in "General" format. I now have run into a someone who helped me solve this. Highlighting the selection (not the column as it kept causing format issues at the end of this process), then going to the Data tab on the upper ribbon and selecting "Text to Columns". Then selecting "Delimited" then selecting "Space" then selecting "Date" & finally selecting the cell the data would be put into separated the mm/dd/yyyy into a column, the hh:mm:ss into a column, & the MDT into a column. I then deleted the columns with the hh:mm:ss & MDT in them. If you know of another way I'd love to hear it too.
James Avery 0 Reputation points

2024-02-03T09:13:18.01+00:00
You can use the "Text to Columns" feature in Excel to separate the date and time into different columns. Here's how:

Select the column containing your dates.

Go to the "Data" tab on the Excel ribbon.

Click on "Text to Columns."

In the wizard that pops up, choose "Delimited" and click "Next."

Uncheck all delimiter options (since you're not splitting based on a delimiter) and click "Next."

In the column data format step, choose "Date" and select the appropriate date format from the dropdown menu. This will ensure Excel recognizes your dates correctly.

Click "Finish."

This should split your date and time into separate columns, allowing you to work with just the date part.

1 answer

Your answer

Barry Schwarz 3,746 Reputation points

2024-02-02T21:31:21.3733333+00:00

Is the value in the cell text or an Excel date-time value? Select one of the cells that display this and tell us what format is being used for that cell.
Curious 0 Reputation points

2024-02-03T02:09:16.31+00:00

It's in "General" format. I now have run into a someone who helped me solve this. Highlighting the selection (not the column as it kept causing format issues at the end of this process), then going to the Data tab on the upper ribbon and selecting "Text to Columns". Then selecting "Delimited" then selecting "Space" then selecting "Date" & finally selecting the cell the data would be put into separated the mm/dd/yyyy into a column, the hh:mm:ss into a column, & the MDT into a column. I then deleted the columns with the hh:mm:ss & MDT in them. If you know of another way I'd love to hear it too.
James Avery 0 Reputation points

2024-02-03T09:13:18.01+00:00

You can use the "Text to Columns" feature in Excel to separate the date and time into different columns. Here's how:

Select the column containing your dates.

Go to the "Data" tab on the Excel ribbon.

Click on "Text to Columns."

In the wizard that pops up, choose "Delimited" and click "Next."

Uncheck all delimiter options (since you're not splitting based on a delimiter) and click "Next."

In the column data format step, choose "Date" and select the appropriate date format from the dropdown menu. This will ensure Excel recognizes your dates correctly.

Click "Finish."

This should split your date and time into separate columns, allowing you to work with just the date part.

Answer 1

Barry Schwarz 3,746

You cells contain text, not Excel dates. The DATEVALUE formula can convert the data to a date value. Try =DATEVALUE(LEFT(your cell,FIND(" ",your cell)-1)). You will need to format the cell with this formula to a suitable date format.

Jiajing Hua 18,065 Reputation points Moderator

2024-02-05T06:20:15.5433333+00:00

Hi @Curious If Barry Schwarz‘s reply is helpful, you may accept it as an answer.

Share via

Excel date, time, time zone extraction

1 answer

Your answer