Solved change am/pm to UTC Excel

Microsoft Office excel 2007
September 21, 2012 at 08:12:53
Specs: Windows Vista
Hello, I am working on excel data that was exported from a program called ODK breifcase. My data is usually time stamped in UTC but since I used the program to export my data, the timestamp is now in AM/PM. For example the cell will read 9/19/2012 13:07 but when I click on the cell the top insert function bar reads 8/19/2012. In any case I want to change these columns to UTC. My data that is in the UTC format reads Thu Aug 16 23:53:45 UTC 2012. How can I change the timestamps in sheets with the am/pm time stamps to match UTC time stamp format mentioned above?

See More: change am/pm to UTC Excel

Report •


#1
September 21, 2012 at 10:55:28
There is no easy way to accomplish this.

Look here for more info and a zip file download that will make life easier.

http://www.cpearson.com/excel/Local...

MIKE

http://www.skeptic.com/


Report •

#2
September 21, 2012 at 13:29:59
✔ Best Answer
If you know how many hours you will need to add to get your UTC time,
then try this:

As an example, if you need to add 5 Hours:

In A1 is the current Date & Time: 9/21/2012 4:22:00 PM

In B1 I add my 5 hours using the formula: =A1+TIME(5,0,0)

B1 now displays the UTC Date & Time: 9/21/2012 9:22 PM

In C1 I enter the formula: =TEXT(B1,"DDD MMM DD HH:MM:SS UTC YYYY")

C1 now displays the Date & Time in UTC format of:

Fri Sep 21 16:22:00 UTC 2012


To use only one cell try this formula:

=TEXT(A1+TIME(5,0,0),"DDD MMM DD HH:MM:SS UTC YYYY")

MIKE

http://www.skeptic.com/


Report •

#3
September 21, 2012 at 13:42:43
To have the Date & Time automatically updated you could also use:

=TEXT(NOW()+TIME(5,0,0),"DDD MMM DD HH:MM:SS UTC YYYY")

MIKE

http://www.skeptic.com/


Report •

Related Solutions


Ask Question