Re formatting Dates problem

[opsman]

Hi

I have a column full of dates copied and pasted in the folowing format;

Jun 30, 2010 10:18 AM

Basically all I want to retrieve in another cell is simply the hour value i.e. 10 and the same running down the column.

I have tried with numerous attempts to reformat, even to cut it down in size slowly etc, but the closest I have got today has been to get the last value in the column to change but the rest remain the same???

I really need a fresh set of eyes to point out the problem here...

any help gratefully received...

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[opsman]

Book 1 attached....

One other issue I have just noted is that the times may need to be in another column first in a 24 hour format, before I can take it down to the single hour? The result will be "somewhere" in an adjacent column but not really before column BW.

[DonkeyOte]

In your sample file (given UK locale) it would be done using:

=HOUR(SUBSTITUTE(REPLACE(E8,1,4,""),","," "&LEFT(E8,3)))

however - I suspect you need to adjust based on the fact this string could be located in various places - I'm not quite sure what you mean by that to be honest (ie workflow)

[DonkeyOte]

edit - of course if you need only the time....

=HOUR(REPLACE(E8,1,12,""))

(doh)

[Excelnoobi]

Hi all

I have the following convoluted problem:

I have a cell with a "Date of Manufacture" value in it eg: 1991. This is a static value. This value will not be edited to change.

In another cell on the same row, I require that the colour of the cell changes to yellow when the age of the static cell is 2 years from becoming 5 years old.
In the same cell, I require that the colour of the cell changes from yellow to orange when the age of the static cell is 1 year from becoming 5 years old.
In the same cell, I require that the colour of the cell changes from orange to red when the age of the static cell becomes 5 years old.
I then require the static cell (red) to change from red to clear when the 5th year changes to 6 years.
I now need the process to repeat itself every 5 years till it reaches an expiry date of 40 years i.e. 10,15,20,25,30,35,40.

Can any genius help...pleeez!

[opsman]

edit - of course if you need only the time....

=HOUR(REPLACE(E8,1,12,""))

(doh)

Beautiful!

Thanks....