Create Formula Using Select Values of a Cell

  • Thread starter Thread starter Harpo
  • Start date Start date
H

Harpo

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.
 
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
 
Give this formula a try...

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