24hr time subtraction

[DevinSutherland]

I really need some help with this one!!!

I am trying to subtract 6hrs off of the Zulu time to equal the Local time , these are the times that I have below.
Its not a hard formula if the number stays above 0600 I am having a problem when the time has to switch back to 2300.

Zulu time Local time
1255 0655
2341 1741
0541 2341
1921 1321
0007 1807

 

[Special-K99]

Mm, this seems to work

=IF(A1>1200,A1-600,A1+1800)

 

[Fishboy]

Mm, this seems to work

=IF(A1>1200,A1-600,A1+1800)

Funnily enough I worked it out the same way, but the other way around!

=IF(A1<1200,A1+1800,A1-600)

 

[DevinSutherland]

here is what I get...
1255 -0600
2341 -0600
0541 2341
1921 -0600
0007 1807

it works great when it is less than 0600 but not for the others

 

[Jonmo1]

Given that your cells contain leading 0's, they're probably numbers stored as text.
So the > and/or < operations are invalid against those Text values.

Try

=IF(A1+0>1200,A1-600,A1+1800)

 

[Jonmo1]

Also, it's taking advantage of a mathematical coincidence because you're subtracting an exact hour value.
This type of structure would fail if you had to include any minutes or seconds

Here's how I'd do it.

=TEXT(MOD(TEXT(A2,"00\:00")-"6:00:00",1),"hhmm")

 

[DevinSutherland]

that first one worked perfect thanks!!!