9 different timzezones

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey guys,

I need some major help here. I want to make a table off hours that our friends can be online for assistance but the problem is that I need to make it in 9 different timezones. I found ut already that I need to use the 1904 system.
Problem 1: I entered all numbers in zulu time since that one never changes. Now I get some negative timevalues. How can I change this? Preferably without macro.
Problem 2: When I enter a formula and the source fielf s empty it will post the timedifference between the zones. I want a blank value in there
Problem 3: On the second sheet I want a simplified table for all hours we can have somebody standing by for all days of the week. What formula do I need to use?
Example:
Monday: 8:00 11:30
14:15 16:00
21:00 23:00
Problem 4: Since he have summer and wintertimes I need a button that will adjust my times on my sheet

Here are the timezones I need:
winter summer
ZULU
MSK z+3 z+4
EET z+2 z+3
CET z+1 z+2
GMT z z+1
EST z-5 z-4
CST z-6 z-5
MST z-7 z-6
PST z-8 z-7

Can someone please please please help me because I don't know where to start anymore
 
Hi Bart!

Let's cover the first two questions first. Or at least I think this is
a correct interpretation of what you want.

A1: Location
B1: Zulu Time
C1: Zulu Difference
D1: Summer
E1: Local Time

Examples:
A2: MSK
B2: 2:00
C2: 3
D2: y
E2:
=IF(B2="","",B2+(C2/24)+((B2+C2/24)<0)+((D2="y"))/24)
Returns 6:00

But if D2 <> y
Returns 5:00

And if A2 is blank returns an empty string ""

The ((B2+C2/24)<0) bit returns TRUE which is coerced to 1 if the time
difference results in a negative time
The ((D2="y"))/24 bit adds 1 hour if summer time is in use.
 
Norman,

the second part of the formula works fine but it the first part still gives me a error when i want it to show a blank cell.
Problem 1 is solved, thank you for that.
Problem 2 I still need to solve
Problem 3 has got something to do with the =min(matrix,-k) function I think Problem 4 I am going to solve with a button.

But its obvious I need some help on doing this.
 
Hi Bart!

We did an amendment offgroup to allow 9:00AM time entry (i.e. no space
between the time and the AM/PM designator).

This needs correcting to:

=IF(B2="","",--(LEFT(B2,LEN(B2)-2)&"
"&RIGHT(B2,2))+(C2/24)+((--(LEFT(B2,LEN(B2)-2)&"
"&RIGHT(B2,2))+C2/24)<0)+((D2="y"))/24)
 
Well, looks like it that it's almost complete now.
The formula is working now, thx for that Norman!
I really appreciate your help in this.
The only part that still needs to be solved is the fact how to get it simplified on a second page...
This is what the table gives me now:
A B C D
16:00 21:00
14:00 16:00
16:00 21:00
16:00 21:00
16:00 0:00
0:00 0:00
0:00 21:00

19:15 20:30 0:00 3:00
19:15 20:30 0:00 3:00
19:15 20:30 0:00 3:00
19:15 20:30 0:00 3:00
19:15 20:30 0:00 3:00
19:15 20:30 0:00 3:00
19:15 20:30 0:00 3:00

17:00 21:00
17:00 20:00
17:00 19:00
17:00 21:00
17:00 23:00
17:00 21:00
17:00 21:00

This is the value from 3 persons from monday till friday. I want on the second page that it displays this:
Monday: 16:00 21:00
Monday: 0:00 3:00

Tuesday: 14:00 16:00
Tuesday: 17:00 20:30
Tuesday: 0:00 3:00

Wednesday: 16:00 21:00
Wednesday: 0:00 3:00

Thursday: 16:00 21:00
Thursday: 0:00 3:00

Friday: 16:00 3:00

Saturday: 0:00 0:00

Sunday: 0:00 21:00

The weeknames are fields i put in: the numbers should be data.
Overlapping hours should be missed. How in Gods name should I do this?
Thx again,
Bart Cuenen
 
Hi Bart!

Nowhere near enough information on days or your objectives or even how
your data is being summarized.

I really think that you need to start from scratch here and address
your mind to how you enter your data and how you want to present and
summarize it. Certainly life is a lot easier if you can enter data in
a format recognized as time by Excel. Parsing of "incorrect" entries
is best left to situations where you have no control over the data
entry.

On summarizing of data, you might look at Pivot Tables as a possible
solution.
 
Back
Top