Convert Text GMT to formatted real GMT format

Discuss the spreadsheet application
Post Reply
User avatar
axelrose
Posts: 145
Joined: Wed Aug 12, 2009 3:51 am

Convert Text GMT to formatted real GMT format

Post by axelrose »

OPENOFFICE CALC related question

I have a long list on a website of times in GMT. I copy then paste them into spreadsheet and they appear as just plain number format as follows...

1200
45
55
120
900
1220
2233

The source of these GMT times are along the left side of the data on this website,
https://www.spc.noaa.gov/climo/reports/200328_rpts.html

They're GMT and in 24 HR mode but how can I make CALC convert these simple values into true GMT Values as follows,,,

12:01
22:30
00:45
etc???

thanks!! :knock: :crazy:
OpenOffice 4.1.13 :bravo: /Windows 10
The Gov't should pay citizen $5K for Tornado shelters.
That's $3.33 PER LIFE Injured or Killed!
An average, 1,500 people injured/killed yearly from Tornadoes
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: HOW 2 Convert Text GMT to formatted real GMT format?

Post by RusselB »

First off, ensure that you get the data using the CSV format, not the RAW TORNADO CSV or by copying & pasting from the website.
Then use

Code: Select all

=VALUE(IF(LEN(A2)=2;"0";LEFT(A2;LEN(A2)-2))&":"&RIGHT(A2;2))
The 1200 would become 12:00, not 12:01 as you posted.
The 45 becomes 00:45 not 22:30
Please ensure, in future, that the data you supply corresponds with the data you're wanting to see.
In order to see the times as a time, you'll need to apply a style or a format to the column with the formula.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Post Reply