FORMATTING/CONVERTING TO ZULU TIME

[MM]

Does anyone know how to format or convert a column of times
(eastern/mountain/central etc....) all to zulu time?
I'm currently using Excel 2003.
Thanks,
MM

 

[Mike H]

Hi,

Eastern, Mountain(Mountain??) and central time will all be offset from Zulu
by an amount of hours so for each column use this with the hours (Plus/minus)
suitably adapted and drag down.

=TIME(HOUR(A1)-6,MINUTE(A1),SECOND(A1))

Mike

 

[MM]

Hi Mike,
Thanks for the quick reply. I'm pretty much self taught...so, needless to
say, I'm green and almost need to be held by the hand through this one.
My columns are F & G. So, I highlight the 2 columns, go under conditional
formatting, formula is and then do I replace your "A" with F? Do I format 1
column at a time? I don't have seconds in my time. I basically have for
example.... 0645, 1815, 1430 etc.... Thanks again for your help.
MM

 

[Fred Smith]

As you've found out, if you want responders to show you the exact formula
you need, then identify the cells you are using in your initial post.
Otherwise, we can only generalize, and tend to use A1 to show the way.

In this post, you talk about conditional formatting. Where did that come in?
I don't see how conditional formatting is going to do what you want.

You say your columns are F and G. What's in F, and what's in G? Do you mean
you have a time in F, and you want to convert it to Zulu in G? If so, apply
Mike's formula (in G1) like:
=time(hour(f1)-6,minute(f1),second(f1))

As you haven't told us what time zone you're using, only you can determine
how many hours you need to subtract (or add) to convert to Zulu.

Do you format 1 column at a time? No. Enter the formula in the first cell in
the column. Format it the way you want. Then copy it down the column (using
the fill handle is the easiest way). The formatting will be copied at the
same time.

Regards,
Fred.

 

[MM]

Hi Fred,
Thanks for the tips on posting "clarity".
Column F is a start time, and G is the end time. The Zulu time will change
from "office" to "office".
Ok, now this part I'm stumped.... how am I supposed to add the formula if
not using conditional formatting? Example:
0700
0840
1035
I have to get these numbers (and others in the column) to change to zulu time.
Thanks so much,
MM

 

[Fred Smith]

Well, we have several issues.

First, I don't understand the fixation on conditional formatting. Formatting
will only change the display of a cell. It won't change the underlying
number. I don't know of any way to use formatting to change a cell from 0840
(Eastern Time, say) to 1340 (Zulu time). So I'd give up on conditional
formatting if I were you.

Next you need to decide whether you want to permanently change the times to
Zulu times, or show Zulu time in a separate column. Most people would add
columns to their spreadsheet to show Zulu time. It's much easier to fix any
errors which occur, and it's easy to see the time difference. If you want to
permanently change the value, it's actually easier to do, but makes fixing
errors much more difficult.

Third, we need to know whether you have a number or and Excel time in your
cell. Most times have a colon in the display, yours doesn't. It will make a
difference in the solution.

Post back with answers to these questions:
1. Do you want to permanently change the values in columns F and G, or not?
2. What is the format you are using? Is it hhmm, or something else?
3. Where do you plan on storing the time difference between the time zone
used in the spreadsheet and Zulu time. In a cell (like B1)? Does it need to
be entered? Do you want to look it up somewhere?

What you want to do can be done, but I think it's going to take more effort
than you expected.

Regards,
Fred.

 

[MM]

Hi Fred,
Sorry for the delay. Our office might be doing something totally different
but I better get the answers I need in case they come back to this idea.
Not fixed at all on the conditional formatting, just thought when I saw a
formula, that this is what needs to be done. I don't know how to apply the
formula given then.
I can either change the times permanently or add a column. Thinking of what
needs to be done now, I think it's best to add a column.
Most of our schedules are handed in the same format, hhmm (no colons).
We are going to scrap column G and stick to start time in column F only to
change to Z-time.
So, for example...
Cell F4 has the start time needed to be changed to zulu time. Cell H4 would
then have the result.
I appreciate any help you can send my way. Like I said, I'm pretty "green"
with Excel and am self-taught.
Thanks again
MM