Converting time

sandramac used Ask the Experts™

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.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

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
Zulu Time ConversionIf you just want numeric values then use below formula, change Left function as per first two digits:

Open in new window

Zulu Time ConversionHope this helps...
I tweaked as per your requirement from here
Hello Sandra

Please chk the file attached value you request is in Col I or the Yellow column.
Here is the formula based on the values you have put in the respective columns

Open in new window

This is the formula in I1 and drag down.



Thank You
Your welcome

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial