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
sandramacAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsDistinguished Expert - 2017Commented:
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
0
gowflowCommented:
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
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sandramacAuthor Commented:
Thank You
1
gowflowCommented:
Your welcome
gowflow
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.