If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
How do I Convert TEXT to EST Time ?
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 |
#3
|
|||
|
|||
How do I Convert TEXT to EST Time ?
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 |
#4
|
|||
|
|||
How do I Convert TEXT to EST Time ?
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 |
#5
|
|||
|
|||
How do I Convert TEXT to EST Time ?
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 |
#6
|
|||
|
|||
How do I Convert TEXT to EST Time ?
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 |
#7
|
|||
|
|||
How do I Convert TEXT to EST Time ?
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 . |
#8
|
|||
|
|||
How do I Convert TEXT to EST Time ?
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 . |
#9
|
|||
|
|||
How do I Convert TEXT to EST Time ?
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 . |
#10
|
|||
|
|||
How do I Convert TEXT to EST Time ?
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 |
|
Thread Tools | |
Display Modes | |
|
|