| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to select values | Excel Discussion (Misc queries) | |||
| Using a formula to select a cell using the row and column values | Excel Worksheet Functions | |||
| select date range then find average of values in another cell | Excel Worksheet Functions | |||
| how can I select two different values for a cell? | Excel Worksheet Functions | |||
| Create a drop down in XL cell to select date | Excel Worksheet Functions | |||
Linear Mode
