Convert Text GMT to formatted real GMT format

Discuss the spreadsheet application

Convert Text GMT to formatted real GMT format

Postby axelrose » Fri Apr 03, 2020 3:45 am

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:
Apache OpenOffice 4.16/Windows 10
The Federal Gov't should pay each citizen $5,000 to build Tornado shelters.
WHY NO ONE CARES? That's $3.33 PER LIFE Injured or Killed!!
An average of 1,500 HUMANS are injured/killed yearly from TORNADOES!

#BS
User avatar
axelrose
 
Posts: 123
Joined: Wed Aug 12, 2009 3:51 am

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

Postby RusselB » Fri Apr 03, 2020 4:15 am

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   Expand viewCollapse view
=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 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 6062
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON


Return to Calc

Who is online

Users browsing this forum: No registered users and 5 guests