asked on 4/11/2017

Converting time

Hello,

Trying to find a way to convert a list of time values in Zulu to local time. Column B has a list of values 01Z, 02Z, 03Z, etc... down to row 20. I need in Column C and D to convert the time over to local time. It would subtract the value from cell G1 from Zulu time and put the numeric digit in Column C and either PM or AM in Column D. I have attached an example.
TIme_Convert.xlsx

Microsoft Office Microsoft Excel Spreadsheets

Last Comment

Jacques Geday

4/11/2017

Shums Faruk

4/11/2017

Hi Sandra,

It won't be possible with just single formula.
First formula extracting numeric values from Col B and converting them into Time Format in E2

=TIME(TRUNC(ABS(LEFT(B2,1))),(ABS(LEFT(B2,1))+12 -TRUNC(ABS(LEFT(B2,1))))*60,0)

Open in new window

Assuming your Hour Difference to GMT is +8, I have added another column F to have Hour Difference, again I will convert Hour difference to Time Format in G2 with below formula:

=TIME(TRUNC(ABS(F2)),(ABS(F2) -TRUNC(ABS(F2)))*60,0)

Open in new window

Final result in H2 with below formula:

=IF(ISERROR(F2),"Unknown TimeZone", IF(F2<0,IF(E2<G2,E2-G2+2*TIME(12,0,0),E2-G2),E2+G2))

Open in new window

Change the Col H format to hh:mm AM/PM

If you just want numeric values then use below formula, change Left function as per first two digits:

=IF(MOD(LEFT(B2,1)+8,24)=12,MOD(LEFT(B2,1)+8,24),MOD(LEFT(B2,1)+8,12))

Open in new window

Hope this helps...
I tweaked as per your requirement from here
Sandra_Zulu-Time_Convert.xlsx

ASKER CERTIFIED SOLUTION

Jacques Geday

4/11/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

sandramac

4/11/2017

ASKER

Thank You

Jacques Geday

4/11/2017

Your welcome
gowflow

Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K

Questions

Followers

Top Experts

Get a personalized solution from industry experts

Ask the experts

"Invaluable tool for our organization"
Josh Regalski
"This is the best money I have ever spent."
@rwheeler23
"Your help has saved me hundreds of hours of internet surfing."
@fblack61
"Just a dang good service!"
@golferski
"Worth every penny."
Steve Hougom
"It’s been a life saver."
Maria Halt
"Best support site I’ve seen!"
Bob Schneider
"I would be lost without them."
@fblack61
"Saved my job multiple times."
Walt Forbes
"I love this site."
Maria Duwe
"Friendly respectful help."
Andrew Kowtalo
"Such top-notch experts."
@magento
"The best money I have ever spent."
@rwheeler23
"Beyond any comprehensible value"
George Morris
"Invaluable tool for our organization"
Josh Regalski
"This is the best money I have ever spent."
@rwheeler23

Read over 600 more reviews

TRUSTED BY