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

Thanks a lot for your help it's working fine.

If you have some time can you explain me this folmula and how it is working?
It will helps us all out.


"Ron Rosenfeld" wrote:


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

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

--ron
.

On Mon, 16 Nov 2009 21:34:31 -0800, fruitchunk
wrote:

Thanks a lot for your help it's working fine.

If you have some time can you explain me this folmula and how it is working?
It will helps us all out.



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


Simply:
1. Convert Time in A1 from "military style" to a format Excel
understands as time.

2. Convert the GMT difference in B1 to a number and compute the
difference from EST.
a. Strip off the GMT portion by starting at the 4th character.
b. Add 5 for EST conversion.
c. Divide by 24 since Excel stores hours as fractions of a
day.
3. Add "1" since Excel does not like negative times.
--ron