How to convert this date 07/12/2007 04:47:32 PM WET to the

[Ed]

Hi All,

Does anyone know how to convert this date: 07/12/2007 04:47:32 PM WET
to the following one : 7/12/2007 12:47

Is there any date command you know I may use?

Thanks,
Ed

 

[Rick Rothstein \(MVP - VB\)]

First off, is that actually a real Excel date in your worksheet, or is it
just Text right now (that is, what does the cell's format show)?

Will all your times be in the same time zone (I'm guessing that is what the
WET is)? If not, you will need to list the possible time zone abbreviations
for us.

When you say "convert", do you mean replace what you showed with what you
want **in the same cell**?

Rick

 

[Begreen]

Hi Rick,


Yes, 07/12/2007 04:47:32 PM WET is just a text right now.


For your second question, yes, when I say to do the conversion, I mean
replacing this Date: 07/12/2007 04:47:32 PM with this one: 7/12/2007
12:47

I think would need a formula to do it, but I don't how to proceed as I
am not yet good on Excel.

In one of my excel sheet, I have the Date above, in all of its rows,
and I want to change all of them and look like this one and its
format: 7/12/2007 12:47

Thanks,
Ed

 

[Ed]

Hi Rick,

Yes, 07/12/2007 04:47:32 PM WET is just a text right now.


For your second question, yes, when I say to do the conversion, I
mean
replacing this Date: 07/12/2007 04:47:32 PM with this one: 7/12/2007
12:47


I think would need a formula to do it, but I don't how to proceed as
I
am not yet good on Excel.


In one of my excel sheet, I have the Date above, in all of its rows,
and I want to change all of them and look like this one and its
format: 7/12/2007 12:47


Thanks,
Ed

 

[Ed]

Hi Rick,

Yes, 07/12/2007 04:47:32 PM WET is just a text right now.
For your second question, yes, when I say to do the conversion, I mean
replacing this Date: 07/12/2007 04:47:32 PM with this one: 7/12/2007
12:47



I think would need a formula to do it, but I don't how to proceed as I
am not yet good on Excel.



In one of my excel sheet, I have the Date above, in all of its rows,
and I want to change all of them and look like this one and its
format: 7/12/2007 12:47


Thanks,
Ed

 

[John C]

Assumptions, you are converting from WET time to your local time always, and
the WET is always at the end. Then, assuming your value is in A1, you could
type the following formula:
=TEXT(--(LEFT(A1,LEN(A1)-4))-TIME(4,0,0),"dd/mm/yyyy hh:mm am/pm")

Hope this helps.
And if you need to refer to it as time again, just but the -- in front of
the cell you have your converted time in.

 

[Rick Rothstein \(MVP - VB\)]

The reason I asked if you wanted to replace the value in the cell is because
you cannot do that with a formula (the cell either has a formula in it or it
has data in it, but it can't have both)... you will need to use a VB macro
to convert it in place. Since you did not list any other time zone
abbreviations, I am guessing that WET is the only one you ever deal with.
Here is a macro that will do your conversion. Right click the worksheet tab
with your data on it and select View Code from the popup menu that appears.
Copy/paste the following into the code window that appeared when you went
into the VB editor...

Sub ConvertWETdates()
Dim C As Range
For Each C In Selection
If Right(C.Value, 3) = "WET" Then
C.Value = DateAdd("h", -4, CDate(Left(C.Value, Len(C.Value) - 3)))
End If
Next
End Sub

To use this macro, select all the cells with your WET dates in them, press
Alt+F8, select ConvertWETdates from the list and click the RUN button.

Rick

 

[Ed]

Rick and John,

All your suggestions worked.Thanks for all your help!

Ed