MID function not working

[mswaf01]

I need to cut a long column of cells (millions) quickly. The current data displayed in the cell its date and time, displayed in a custom format of dd/mm/yy hh:mm

I need to quickly cut this in a new column that only contains the hour. However, the MID function isn't working for this:

. I have also tried this using MID and LENS together.

I think the problem is that the DATETIME is in a custom format rather than general, but when I convert to general it reverts to a series of seemingly unrelated numbers, e.g.:

What do I need to do to be able to extract only the hour data?

 

[Fluff]

Hi & welcome to MrExcel.

when I convert to general it reverts to a series of seemingly unrelated numbers

That's exactly what should happen, a date is just a number with special formatting where 1 is the 1st Jan 1900.
As they appear to be genuine dates & times try

=hour(c6)

 

[mswaf01]

Hi & welcome to MrExcel.

That's exactly what should happen, a date is just a number with special formatting where 1 is the 1st Jan 1900.
As they appear to be genuine dates & times try

=hour(c6)

Thanks, that's worked brilliantly! (I didn't know that was a function).

I've got a new problem now....about halfway down my spreadsheet the formula stops working. I think its because the format of the DATETIME cells is obviously suddenly different, but when I try to change it to the same as the other cells it doesn't work and just stays the way it is?

 

[Fluff]

The rows where you get the #value error are text & not real dates/times. Where is this data coming from?

 

[mswaf01]

The rows where you get the #value error are text & not real dates/times. Where is this data coming from?

Its data provided to me from an independent source that collected it.

 

[Fluff]

Is it supplied as a csv?

 

[mswaf01]

Is it supplied as a csv?

Yes- I've also had to split the file in SPSS because it was too large.

 

[Fluff]

What is SPSS?

 

[mswaf01]

What is SPSS?

It's just another processing programme- it shouldn't be the cause of the problem. I just used it to split the file in half so it could be opened in Excel without data loss.

 

[Fluff]

It may well be the problem as it could be causing the dates to change to text.