Reformat and delete text in a column

[JBroussard1964]

This is the text I need to edit

2024-09-19T10:27:00-05:00


I need to Delete the "T", Reformat the Date to MM/DD/YYYY, add a space and reformat the Time to HH:MM (24 hr clock) and delete "-5:00.



Final Result should look like this

09/19/2024 10:27

[Fluff13]

How about

Please Login or Register  to view this content.

and format the cell to your needs.

[Rick Rothstein]

I believe the format of your data always has leading zeros for single digit elements as a standard. Given that , here is another formula that will work in any version of Excel...

Please Login or Register  to view this content.

[JBroussard1964]

Thanks for the quick response. Those all let to producing errors. I've attached the file so y'all can see more specifically what I have. My apologies, I have not done this stuff in many many years.

[Fluff13]

They both work for me

[JBroussard1964]

I am so confused. I copied the formula from my clipboard and onto column C and I get the error #N/A. The format of data in Column B is "General". What am I doing wrong?

[Fluff13]

Did you change the range in the formula to look at B2?

[JBroussard1964]

Thank You. No I hadn't done that. Now the result in column C is as follows 45554.43542. How to I change the format for the date and time?

[Fluff13]

You can apply a cutom format, have a look at the workbook I supplied.

[JBroussard1964]

You are a genius Thank you so very much. Wow ! Thank you.

[Fluff13]

Glad to help & thanks for the feedback.

[JBroussard1964]

Is there any way to deleted column B after the reformatted data is in column C?

[Fluff13]

You would need to copy/paste the formula in col C as values. Then you could delete col B

[JBroussard1964]

How to I make it ignore blanks?

[Rick Rothstein]

.
Whose ever formula you use, just wrap it in and IFERROR function call...

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[JBroussard1964]

I'm getting so close with the forum's assistance. I thank you for your time. I'm down to the wire on my deadline and need some help with a few last things. This is what I need to finish up on and need your help.

This is what I need for my end result

Select macro that will: Supervisor said it must be either a macro or a Script as long as it can be shared by multiple users. We do have a shared drive that can be used for storage.


Increase columns to be 25 pt wide

Add columns between B&C, E&F, and G&H (to place the formula in. Is there a formula that can do that?

In the new column B Reformat the raw data in Time/Date columns - Remove "t" and -5.00, and ignore blank rows using =IFERROR(SUBSTITUTE(TEXTBEFORE(B2,"-",-1),"T"," ")+0,"")

Format result columns to Custom M/d/yyyy h:mm

Copy the above to Columns B, D, F and G

Rename Column F to Event Date, Column H to Damage Site Arrival Time and Column I to Event Date

Finally delete original columns B,D,F&G (the columns with the raw date Year-Month-Day,"T", Hour and Minute and keep the new data in the adjacent columns

The final report should be in Calibri 11 pt ( I noticed the above formula didn't keep the original font.

Thank you.

[Fluff13]

As this is a completely different question, you would be better off starting a new thread.