Create Formula Using Select Values of a Cell
 

Hello,

I'm trying to create a formula using only select values of a cell.

For example cell G1 has the following data:

12/Nov/2009 1620ZULU

I'd like to create a formula which deducts five hours from the zulu time.
Essentially I'd like the cell to read:

12/Nov/2009 1120EST

Please help! Thanks in advance.

Create Formula Using Select Values of a Cell
 

Try the below formula with the text in cell A1

=TEXT(DATEVALUE(LEFT(A1,FIND(" ",A1)-1))+
TIME(MID(A1,FIND(" ",A1)+1,2),
MID(A1,FIND(" ",A1)+3,2),0)-(5/24),"dd/mmm/yyyy hhmm") & "EST"

If this post helps click Yes
---------------
Jacob Skaria


"Harpo" wrote:

Hello,

I'm trying to create a formula using only select values of a cell.

For example cell G1 has the following data:

12/Nov/2009 1620ZULU

I'd like to create a formula which deducts five hours from the zulu time.
Essentially I'd like the cell to read:

12/Nov/2009 1120EST

Please help! Thanks in advance.

Create Formula Using Select Values of a Cell
 

Will the time portion *always* be 4 digits?

--
Biff
Microsoft Excel MVP


"Harpo" <Harpo @discussions.microsoft.com wrote in message
...
Hello,

I'm trying to create a formula using only select values of a cell.

For example cell G1 has the following data:

12/Nov/2009 1620ZULU

I'd like to create a formula which deducts five hours from the zulu time.
Essentially I'd like the cell to read:

12/Nov/2009 1120EST

Please help! Thanks in advance.

Create Formula Using Select Values of a Cell
 

Give this formula a try...

=TEXT((LEFT(A1,LEN(A1)-6)&":"&MID(A1,
LEN(A1)-5,2))-5/24,"d/mmm/yyyy hhmm""EST""")

--
Rick (MVP - Excel)


"Harpo" <Harpo @discussions.microsoft.com wrote in message
...
Hello,

I'm trying to create a formula using only select values of a cell.

For example cell G1 has the following data:

12/Nov/2009 1620ZULU

I'd like to create a formula which deducts five hours from the zulu time.
Essentially I'd like the cell to read:

12/Nov/2009 1120EST

Please help! Thanks in advance.