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?

 

[Brian from Maui]

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?

How are you entering times?

 

[K_Man95]

[/quote]How are you entering times?[/quote]

I am entering the times in ZULU (military) format. So the range of times go from:

0000Z - 2359Z (this is related to 1200a -1159p). The results I want need to be in military format as well. So, they should have the same range 0000L - 2359L.

Does that help?

 

[Andrew Poulsom]

Try the formula eg:

=MOD((A1+1)-(6/24),1)

where A1 contains the ZULU time in the form hh:mm.

By the way 01:00 less 6 hours is 19:00 not 20:00.

 

[K_Man95]

Try the formula eg:

=MOD((A1+1)-(6/24),1)

where A1 contains the ZULU time in the form hh:mm.

By the way 01:00 less 6 hours is 19:00 not 20:00.

This does not work the way that I am wanting the formula to work. This gives a return value in decimal format... NO MATTER what I change the time to in A1. Military time does not include the : between the numbers. For example, the ZULU time for 11:00 pm is related to 2300 and so for from 1200 am until 1159 pm. So, 0000 would be considered 12 midnight and 0800 is 8:00 am and 1700 is 5:00 pm and 2000 is 8:00 pm and so forth. When I enter the time in A1 as 2300, I am hoping to see 1700 in B1. Does this make sense???????

 

[barry houdini]

Andrew's formula converts 02:00 to 20:00

Are you saying that this isn't what you want?

You want to enter 0200 and return 2000?

in which case try

=TEXT(MOD(A1+1800,2400),"0000")

 

[Brian from Maui]

If you're literally entering the time without the colons, Excel will not recognize it as a valid time. Try entering times with the colon. Or you'll have to custom format the cells, then convert to a value Excel recognizes.

 

[K_Man95]

[quote="Are you saying that this isn't what you want?

You want to enter 0200 and return 2000?[/quote]

Sorry for the confusion. The project I am working on is all in military time. Meaning that for you and I, if we look at our watch and we see the time as 4:00 pm, a military person would say it is 1600 hours. Right? So, if you base that on GMT standards or ZULU time, you and I would see 4:00 pm, military would see 1600 hours LOCAL time which relates to 2200 hours ZULU time - off the WORLD clock. So, what I am trying to do is if in Column A the user enters a time in ZULU time, then Column B would reflect the LOCAL time to their current time zone. So, if I am in, say, the Midwest, if I saw a notation that said 2000Z time in Column A, I could look into the adjacent cell in Column B and see 1400L for the current time I am in based on my current time zone. Or if I was on the East Coast, If I saw in Column A 2300Z, then in B1 I would see 1800L .

Does this help?

 

[barry houdini]

Over here in the UK when we use "military time" (normally referred to as "the 24 hour clock") we still use a colon, e.g. 20:00 BUT if you want to convert 0200 to 2000 use my formula above, i.e.

=TEXT(MOD(A1+1800,2400),"0000")

To convert 0200Z to 2000L use

=TEXT(MOD(LEFT(A1,4)+1800,2400),"0000L")

 

[K_Man95]

Over here in the UK when we use "military time" (normally referred to as "the 24 hour clock") we still use a colon, e.g. 20:00 BUT if you want to convert 0200 to 2000 use my formula above, i.e.

=TEXT(MOD(A1+1800,2400),"0000")

To convert 0200Z to 2000L use

=TEXT(MOD(LEFT(A1,4)+1800,2400),"0000L")

THIS IS IT!! CHEERS!!! This site is as good as those like you all that helped. Thanks for your patience and help!

Off to share the wealth of my new found knowledge.....