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

Carrie Ward 20

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

Accepted answer

2 additional answers

Your answer

Answer 1

Alina-Le 1,040 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

Carrie Ward 20 Reputation points

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 3,836 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 1,040 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!

Answer 2

Ashish Mathur 99,575 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

Carrie Ward 20 Reputation points

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

Not sure what you mean

Answer 3

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.

Share via

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

2 additional answers

Your answer