Converting ZULU to LOCAL time

[K_Man95]

I am looking to try and convert an entered ZULU time into a standard LOCAL time format. For example: 2200 ZULU = 1600 Local ; 1000 ZULU = 0400 Local. There is an 0600 difference between ZULU and Local... at least there will be once daylight savings kicks in. Is there a fomula that will work? I have tried to come up with the cell formula, but as the time advances, the variable changes. For example: If I have a time of 0100 ZULU, this equals 2000 Local. The formula to get this would be (0100 - 0600 = -0500 + 1500 = 2000 Local), but... if the time advances (0200 ZULU - 0600 = -0400 + 1700 = 2100 Local), the variable changed from 1500 to 1700. Also, if you have 0300 then that becomes (0300 ZULU - 0600 = -0300 + 1900 = 2200 Local). I can see the pattern, but I don't know how to put that into a formula. Or is there a VB code that would work? I thought a formula would be better, but I can't figure it out.

Any help out there?

 

[Yogi Anand]

Hi K_Man95:

Here is a little simplified version of Barry's formula ...

=MOD(LEFT(A2,4)+1800,2400)&"L"

 

[barry houdini]

Yogi,

I'm not sure that will work, what happens if A2 is 0700 (or 0700Z)?

 

[excel_ignorant]

Re: Converting ZULU to LOCAL time with 4.5 hours ahead of zulu

I've tried to figure out how to use this when trying to convert Zulu, (in the hhmm military format), to local time that is 4.5 hours ahead of zulu. I just can't do it. Please help!!!!!!