How do I Convert TEXT to EST Time ?

I get a text feed with some data, in Column A I have the time (as text) in
Column B I have the timezone. How can I get EST in the next coulmn?

Example:
CELL A1: 1649
CELL B1: GMT -6
In CELL C1 I would like to see 5:49 PM

Thanks

I'm assuming from your example that EST is GMT-7. If that is the case,
then you can put this formula in C1:

=(INT(A1/100)+RIGHT(B1,LEN(B1)-FIND("-",B1)+1)+7)/24+MOD(A1,100)/24/60

Format the cell using Time and 1:30 PM as the style.

Hope this helps.

Pete

On Nov 12, 10:53*pm, fruitchunk
wrote:
I get a text feed with some data, in Column A I have the time (as text) in
Column B I have the timezone. How can I get EST in the next coulmn?

Example:
CELL A1: 1649
CELL B1: GMT -6
In CELL C1 I would like to see 5:49 PM

Thanks

On Thu, 12 Nov 2009 14:53:01 -0800, fruitchunk
wrote:

I get a text feed with some data, in Column A I have the time (as text) in
Column B I have the timezone. How can I get EST in the next coulmn?

Example:
CELL A1: 1649
CELL B1: GMT -6
In CELL C1 I would like to see 5:49 PM

Thanks

=TEXT(A1,"00\:00")-(MID(B1,FIND("-",B1),4)+5)/24

Note the last "5" in the sequence. That is the difference between GMT and EST.
--ron

On Thu, 12 Nov 2009 15:42:27 -0800 (PST), Pete_UK wrote:

I'm assuming from your example that EST is GMT-7.

EST (Eastern Standard Time) is GMT-5, Pete.

I ASSumed that the time in A1 was CST (Central Standard Time in the US)

GMT-7 would be MST in the US (Mountain Standard Time)
--ron

Thanks for that, Ron.

And your formula was more elegant than mine, too. bg

Pete

On Nov 13, 12:46*am, Ron Rosenfeld wrote:
On Thu, 12 Nov 2009 15:42:27 -0800 (PST), Pete_UK wrote:
I'm assuming from your example that EST is GMT-7.

EST (Eastern Standard Time) is GMT-5, Pete.

I ASSumed that the time in A1 was CST (Central Standard Time in the US)

GMT-7 would be MST in the US (Mountain Standard Time)
--ron

On Thu, 12 Nov 2009 18:02:57 -0800 (PST), Pete_UK wrote:

Thanks for that, Ron.

And your formula was more elegant than mine, too. bg

Pete

Thank you, Pete.

I vacillate between using numeric vs text-based approaches to problems like
this. But with text-based approaches, I don't really have to worry about
rounding issues. And I also like the MID function ever since I figured out
that it didn't care if the number of characters took you past the end of the
word.
--ron

I still have a problem with times that are not -GMT for example:
0303 GMT 11

Thanks

"Ron Rosenfeld" wrote:

On Thu, 12 Nov 2009 14:53:01 -0800, fruitchunk
wrote:

I get a text feed with some data, in Column A I have the time (as text) in
Column B I have the timezone. How can I get EST in the next coulmn?

Example:
CELL A1: 1649
CELL B1: GMT -6
In CELL C1 I would like to see 5:49 PM

Thanks

=TEXT(A1,"00\:00")-(MID(B1,FIND("-",B1),4)+5)/24

Note the last "5" in the sequence. That is the difference between GMT and EST.
--ron
.

Ok, I think I figured it out:
=TEXT(A1,"00\:00")-(RIGHT(B1, LEN(B1)-3)+5)/24
seems to be working fine.

Thanks again.

"fruitchunk" wrote:

I still have a problem with times that are not -GMT for example:
0303 GMT 11

Thanks

"Ron Rosenfeld" wrote:

On Thu, 12 Nov 2009 14:53:01 -0800, fruitchunk
wrote:

I get a text feed with some data, in Column A I have the time (as text) in
Column B I have the timezone. How can I get EST in the next coulmn?

Example:
CELL A1: 1649
CELL B1: GMT -6
In CELL C1 I would like to see 5:49 PM

Thanks

=TEXT(A1,"00\:00")-(MID(B1,FIND("-",B1),4)+5)/24

Note the last "5" in the sequence. That is the difference between GMT and EST.
--ron
.

Sorry, I still have a few errors, I will give you actual data:
0017 GMT 8
0045 GMT 1
0256 GMT 11
0856 GMT 10
0902 GMT 13
0154 GMT 5.5
For the above I get #######, for all other records I get the correct answers.




"fruitchunk" wrote:

Ok, I think I figured it out:
=TEXT(A1,"00\:00")-(RIGHT(B1, LEN(B1)-3)+5)/24
seems to be working fine.

Thanks again.

"fruitchunk" wrote:

I still have a problem with times that are not -GMT for example:
0303 GMT 11

Thanks

"Ron Rosenfeld" wrote:

On Thu, 12 Nov 2009 14:53:01 -0800, fruitchunk
wrote:

I get a text feed with some data, in Column A I have the time (as text) in
Column B I have the timezone. How can I get EST in the next coulmn?

Example:
CELL A1: 1649
CELL B1: GMT -6
In CELL C1 I would like to see 5:49 PM

Thanks

=TEXT(A1,"00\:00")-(MID(B1,FIND("-",B1),4)+5)/24

Note the last "5" in the sequence. That is the difference between GMT and EST.
--ron
.

On Sun, 15 Nov 2009 09:02:01 -0800, fruitchunk
wrote:

Sorry, I still have a few errors, I will give you actual data:
0017 GMT 8
0045 GMT 1
0256 GMT 11
0856 GMT 10
0902 GMT 13
0154 GMT 5.5
For the above I get #######, for all other records I get the correct answers.



Now with more information as to your requirements, try this:

=1+TEXT(A1,"00\:00")-(MID(B1,4,5)+5)/24

--ron