How do I Convert TEXT to EST Time ?

[fruitchunk]

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

 

[Pete_UK]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Pete_UK]

Thanks for that, Ron.

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

Pete

 

[Ron Rosenfeld]

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

 

[fruitchunk]

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

Thanks

 

[fruitchunk]

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]

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.

 

[Ron Rosenfeld]

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

 

[fruitchunk]

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]

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