Converting Greenwich Mean Time

[RudeRam]

I using thefollowing formula : A1+7/24 to convert Greenwich Mean Time to my local time zone (MST). It works great for times from 0701-0000, but from 0001-0700 I get the ###### mask. I am confused as to why it work for 17 hours but not the remaining 7 hours. The cells are formatted as hhmm.

Thanks in advance for any help

 

[PA HS Teacher]

Maybe it is a problem when A1 + 7/24 is bigger than 1 day.

How about
=If( (A1+7/24) > 1, 1 -(A1+7/24), A1 + 7/24)

 

[RudeRam]

CORRECTION TO ORGINAL POST

Formula shourd be A1-7/24 not as it is written in the post,

PA HS I tried your suggestion and that works for the hours I am having trouble with when used for the other hours it converts the time forward vise backwards. Thank you for that input I will sit down and monkey with this some more

Thanks again to all

 

[Brian from Maui]

See,

http://www.cpearson.com/excel/timezone.htm

 

[RudeRam]

Brian,

Thank you again you always know where to find the answers that alude me

 

[Barry Katcher]

Here's a partial time-zone table and some baubles to play around with.

KEEPERS.xls
	A	B	C	D	E	F	G	H	I	J	K
4		Pacific	Mountain	Central	EST	Atlantic	Spumoni	Quiche	Pasta	Fongool	Europe
5	Current	9:13PM	10:13PM	11:13PM	12:13AM	1:13AM	2:13AM	3:13AM	4:13AM	5:13AM	6:13AM
6	Input	12:56PM	1:56PM	2:56PM	3:56PM	4:56PM	5:56PM	6:56PM	7:56PM	8:56PM	9:56PM
7
8					Current
9					Time
10					Central
11					11:13PM
12
13					Input
14					Time
15					Pacific
16					12:56PM
Time Zones