GMT/UTC to local time conversion

tuspilica

New Member
Joined
Feb 9, 2016
Messages
16
Hi guys,

I'm trying to convert from GMT time to local time. I have the a column where my data has the format:
19-01-2019,07:20:23

I applied the formula:
=A2-(5/24)
to calculate the local time but i got the error: There's something wrong with the way your formula is typed. Or, there's something wrong with the cells you are referencing."

I tried to modify the cell format, replaced comma with space, dashes with slash ... no success. The format remains the same and the error is present.
I've added a Custom format:
dd/mm/yyyy hh:mm:ss
I applied it to entire column, but there is no change, the data remains with the same format 19-01-2019,07:20:23
It works if i'm typing the full date manually, but the table comes populated with these data
Any help please?

Thank you
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,487
Office Version
365, 2010
Platform
Windows, Mobile
What does the formula =ISNUMBER(A2) return?
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
2,924
.
Code:
=RIGHT(A2,8)-5/24
Format cell/s : TIME 1:30 pm
 

tuspilica

New Member
Joined
Feb 9, 2016
Messages
16
I found out that the date & time data from my entire time stamp column is actually "text" (from a .CSV file).

19-01-2019,07:20:23

So, I have to find the easiest way to convert this text data into time format, then to apply the GMT correction.

Is there a way to do this without using macros?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,487
Office Version
365, 2010
Platform
Windows, Mobile
I found out that the date & time data from my entire time stamp column is actually "text" (from a .CSV file).
n

That is why I asked the question in post number 2 :biggrin:
Try selecting the column and then doing a Find/Replace with a comma in the Find what box and and a single space in the Replace with box (making sure that match entire cell contents is not checked).
 

tuspilica

New Member
Joined
Feb 9, 2016
Messages
16
n

That is why I asked the question in post number 2 :biggrin:
Try selecting the column and then doing a Find/Replace with a comma in the Find what box and and a single space in the Replace with box (making sure that match entire cell contents is not checked).
Thank you Mark!
problem resolved. Appreciating your time and help!
 

Forum statistics

Threads
1,082,099
Messages
5,363,129
Members
400,720
Latest member
Pettel

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top