World Calling times Help

[Joneye]

Humour me as i know web pages and outlook can do this, im battling with working out how time and forumlas work and probably over complicating it.


Is there anyway and instead of me using 3 cells to input hours, mins and secs seperatly, can i just write 9:00:00am in cell B2, then add 4 hours to that time which will inform me the time i need to start my call in Hong Kong.

Country / City	Hour	Min	Sec		Hour	Min	Sec	Time	Calling Time in Hong Kong
Dubai	9	0	0		4	0	0	=TIME(B2,C2,D2)	=I2+TIME(F2,0,0)
South Africa	9	0	0		6	0	0	=TIME(B3,C3,D3)	=I3+TIME(F3,0,0)
UK	9	0	0		8	0	0	=TIME(B4,C4,D4)	=I4+TIME(F4,0,0)
EU	9	0	0		7	0	0	=TIME(B5,C5,D5)	=I5+TIME(F5,0,0)
Brazil	9	0	0		11	0	0	=TIME(B6,C6,D6)	=I6+TIME(F6,0,0)
New York (EDT)	9	0	0		12	0	0	=TIME(B7,C7,D7)	=I7+TIME(F7,0,0)
Chichargo (CDT)	9	0	0		13	0	0	=TIME(B8,C8,D8)	=I8+TIME(F8,0,0)
Salt Lake City (MDT)	9	0	0		14	0	0	=TIME(B9,C9,D9)	=I9+TIME(F9,0,0)
Los Angeles (PDT)	9	0	0		15	0	0	=TIME(B10,C10,D10)	=I10+TIME(F10,0,0)

 

[awoohaw]

if you are looking for data entry and not formulas (the above TIME(hh:mm:ss) is not really a formula.

format the cell as hh:mm:ss. if you don't want to type the hours and minutes and colon:
enter the time as a fraction of a day, 9am = 9/24, 9:30 am = 9.5/24.


For current time just do CNTR-SHIFT-COLON and the current time is entered into the cell

 

[Joneye]

Cheers and good starting point. Colmun D, i have to manually put in 0,0 to my formula, is there an easier way of adding hours in column C to Hour in column B ?

Country / City	Hour	Hour	Calling Time in Hong Kong
Dubai	0.375	4	=B20+TIME(C20,0,0)
South Africa	0.375	6	=B21+TIME(C21,0,0)
UK	0.375	8	=B22+TIME(C22,0,0)
EU	0.375	7	=B23+TIME(C23,0,0)
Brazil	0.375	11	=B24+TIME(C24,0,0)
New York (EDT)	0.375	12	=B25+TIME(C25,0,0)
Chichargo (CDT)	0.375	13	=B26+TIME(C26,0,0)
Salt Lake City (MDT)	0.375	14	=B27+TIME(C27,0,0)
Los Angeles (PDT)	0.375	15	=B28+TIME(C28,0,0)

 

[awoohaw]

just divide column C by 24 as well and add. But you need to be aware that if the sum of B and C is greater than 24 hours you will restart from time 0 (midnight), although the value will have one more day. If you want to see total hours, format the cell as [hh]:mm.