Home > Enterprise >  Formatting a cell containing a date and time with an offset
Formatting a cell containing a date and time with an offset

Time:01-26

How to format Excel cell to force Excel to use it as datetime. Cell contents: 2023-01-24 23:06:55 01

I expect something like: yyyy-mm-dd hh:mm:ss but how to set timezone offset? Thanx

CodePudding user response:

I think the numberformat can't calculate. You might need to add a helper column to calculate the new time. That you can format.

enter image description here

If you want to replace the date text string into a real date you could do like in the picture below. But there are many ways to do it. Simple formating is not possible you have to remove the offset first. So a helper column is needed for this.

=TEXTBEFORE(A5;{" ";"-"};3;;1) 0

replace semicolon with comma if your Excel version needs.

enter image description here

  • Related