Avatar of sandramac
sandramac
 asked on

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 OfficeMicrosoft ExcelSpreadsheets

Avatar of undefined
Last Comment
gowflow

8/22/2022 - Mon
Shums Faruk

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
ASKER CERTIFIED SOLUTION
gowflow

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
sandramac

ASKER
Thank You
gowflow

Your welcome
gowflow
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy