How do i convert this to a normal date and time for easier viewing

Question

How do i convert this to a normal date and time for easier viewing

Anonymous

2025-07-03T08:00:00.000-04:00+ISO 8601 standard

I need to format this in a normal date/time format for easy viewing

Locked Question. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Answer accepted by question author

Alina-Le 6,390 Microsoft External Staff Moderator

Good day @Carrie Ward

Welcome to Q&A forum!

I understand ISO 8601 timestamps are not exactly user-friendly for quick reading. Let me break down your timestamp:

2025-07-03T08:00:00.000-04:00

This is in ISO 8601 format, and here's what each part means:

2025-07-03 → Date (July 3, 2025)
T08:00:00.000 → Time (08:00:00 AM)
-04:00 → Time zone offset (Eastern Daylight Time, 4 hours behind UTC)

It can be converted to a more readable format:

July 3, 2025, at 8:00 AM (EDT)
Or if you prefer 24-hour format: 2025-07-03 08:00 EDT

Here’s how to format ISO 8601 timestamps in Excel:

Step 1: Paste the timestamp

Paste your ISO 8601 value (e.g., 2025-07-03T08:00:00.000-04:00) into a cell (A1)

Step 2: Use a formula to extract and convert

Excel doesn’t natively recognize the full ISO 8601 format with timezone, so we’ll clean it up first.

In a new cell, use this formula to extract the date and time part:

=TEXT(DATEVALUE(LEFT(A1,10)) + TIMEVALUE(MID(A1,12,8)), "mmmm d, yyyy hh:mm AM/PM")

Replace A1 with the cell containing your timestamp.

User's image

(The -04:00 at the end means the time is already in Eastern Daylight Time (EDT), which is the standard for en-US East Coast during summer)

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

User's image

Anonymous

2025-08-11T17:58:15.11+00:00

What if I want to drag the formula down on a column in excel?

So I need the formula in this column so I can drag it down
Barry Schwarz 5,941 Reputation points

2025-08-11T20:15:44.5066667+00:00

As shown in the answer, you do not enter the formula in the cell with the value to be formatted. You enter it in a different cell where you want to see the results of the formatting. If you have an array of values, then you need an array of cells to view the new format. If your array is inA1 to A12 and you enter the in B3 as shown, you can then drag the formula down to cells B4 to B14. The reference to cell A1 in B3 will change automatically to A2 in cell B4 and similarly in the other cells you drag to. B14 will reference A12 and you will have the corresponding array of formatted values you can view.
Alina-Le 6,390 Reputation points Microsoft External Staff Moderator

2025-08-12T08:11:09.83+00:00

Good day @Carrie Ward

I will do it in Excel so that it's easier for you to visualize and follow along. You need to paste the formula I provided earlier.

=TEXT(DATEVALUE(LEFT(A1,10)) + TIMEVALUE(MID(A1,12,8)), "mmmm d, yyyy hh:mm AM/PM")

(You can replace A1 with the cell where you want to start pasting this date format)

In my practice, I start with cell A1. The formula in cells B1 will apply to A1 and correspond to the other cells from A1 to A12 when you paste the formula into B1 and drag it down to the remaining cells (B2 to B12).

Hope you can do it.

Stay safe & happy!
Anonymous

2025-08-18T17:58:58.3666667+00:00

On the contrary - ISO 8601 is quite easy to understand, because it is predictable and consistent globally. That is why it was selected as a Standard. Microsoft should support the standards created by the international standards organizations!
Anonymous

2025-08-18T20:33:57.5066667+00:00

Perfect answer from Alina-Le

4 additional answers

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Anonymous

2025-08-11T17:58:15.11+00:00

What if I want to drag the formula down on a column in excel?

So I need the formula in this column so I can drag it down
Barry Schwarz 5,941 Reputation points

2025-08-11T20:15:44.5066667+00:00

As shown in the answer, you do not enter the formula in the cell with the value to be formatted. You enter it in a different cell where you want to see the results of the formatting. If you have an array of values, then you need an array of cells to view the new format. If your array is inA1 to A12 and you enter the in B3 as shown, you can then drag the formula down to cells B4 to B14. The reference to cell A1 in B3 will change automatically to A2 in cell B4 and similarly in the other cells you drag to. B14 will reference A12 and you will have the corresponding array of formatted values you can view.
Alina-Le 6,390 Reputation points Microsoft External Staff Moderator

2025-08-12T08:11:09.83+00:00

Good day @Carrie Ward

I will do it in Excel so that it's easier for you to visualize and follow along. You need to paste the formula I provided earlier.

=TEXT(DATEVALUE(LEFT(A1,10)) + TIMEVALUE(MID(A1,12,8)), "mmmm d, yyyy hh:mm AM/PM")

(You can replace A1 with the cell where you want to start pasting this date format)

In my practice, I start with cell A1. The formula in cells B1 will apply to A1 and correspond to the other cells from A1 to A12 when you paste the formula into B1 and drag it down to the remaining cells (B2 to B12).

Hope you can do it.

Stay safe & happy!
Anonymous

2025-08-18T17:58:58.3666667+00:00

On the contrary - ISO 8601 is quite easy to understand, because it is predictable and consistent globally. That is why it was selected as a Standard. Microsoft should support the standards created by the international standards organizations!
Anonymous

2025-08-18T20:33:57.5066667+00:00

Perfect answer from Alina-Le

Answer 1

Anonymous

This is not an acceptable answer!
Why is Microsoft unwilling to support established International Standards? These are created to make it easier to share data between applications - not harder.

Stefan Blom 347.8K Reputation points MVP Volunteer Moderator

2025-08-18T18:00:14.27+00:00

Why is Microsoft unwilling to support established International Standards?

Note that Microsoft developers do not read these forums. To suggest changes in Excel, you can post feedback via Help > Feedback in Excel and/or via the portal at https://feedbackportal.microsoft.com/feedback/forum/fb6d67e3-301c-ec11-b6e7-0022481f8472 .
HansV 462.7K Reputation points MVP Volunteer Moderator

2025-08-18T20:45:16.99+00:00

Power Query (Data > Get & Transform) does recognize ISO 8601 date/time values

Answer 2

Ashish Mathur 102.3K Volunteer Moderator

Hi,

Here is what worked for me

Select the range and press Ctrl+H. In the Find What box, type T and click on Replace All
In the Find What box, type .* and click on Replace All

Hope this helps.

User's image

Anonymous

2025-08-11T17:59:15.04+00:00

Not sure what you mean

Answer 3

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Answer 4

User's image

Hi,

In cell C2 apply this formula:

=LET(d, TEXTSPLIT(TEXTJOIN(";",, SUBSTITUTE(A2:A15, "T", "-")), "-", ";"), DATE(CHOOSECOLS(d, 1), CHOOSECOLS(d, 2), CHOOSECOLS(d, 3)) + TIMEVALUE(CHOOSECOLS(d, 4)))

Then select the range C2:C13 and open window of Format Cells and click Custom. In Type box write this:

mmmm d, yyyy hh:mm AM/PM

click Ok.

Change the range in formula as per you need.

Note: I changed some data in column A just to test the formula and see if it works or not and I think it does.

Of course, you can't mark my answer even if you find it valuable, since you've already marked a previous answer, but regardless, I hope the formula is useful to you.

Have a nice day.