Converting time

sandramac
sandramac used Ask the Experts™
on
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
Comment
Watch Question

Do more with

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

Commented:
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:
=IF(MOD(LEFT(B2,1)+8,24)=12,MOD(LEFT(B2,1)+8,24),MOD(LEFT(B2,1)+8,12))

Open in new window

Zulu Time ConversionHope this helps...
I tweaked as per your requirement from here
Sandra_Zulu-Time_Convert.xlsx
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
=TIME(HOUR(NOW())+VALUE($G$1)+VALUE(LEFT(B1,LEN(B1)-1)),MINUTE(NOW()),SECOND(NOW()))

Open in new window


This is the formula in I1 and drag down.

gowflow
TIme_Convert.xlsx

Author

Commented:
Thank You
Your welcome
gowflow

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