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?

 

[Alex Blakenburg]

It looks to me that the dates in your source file are US format of mm/dd/yyyy and that your local format is dd/mm/yyyy.
Hence dates with days > 12 are being read as text and those <=12 are being treated as dates (but with the day and month reversed).

I am sure Fluff can give you a more efficient formula but in the meantime give this a try.

Book1
	A	B	C
1	Orig Date (US)	Date (Local)	Hour
2	01/13/2023 14:00:01	13/01/2023	14
3	1/12/2023 15:00	12/01/2023	15
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=LET(dt,TEXT($A2,"dd/mm/yyyy hh:mm:ss"), DATE(MID(dt,7,4),LEFT(dt,2),MID(dt,4,2)))
C2:C3	C2	=LET(dt,TEXT($A2,"dd/mm/yyyy hh:mm:ss"), VALUE(MID(dt,12,2)))

 

[proficient]

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.

Try this.

=SUBSTITUTE(HOUR(C6),CHAR(160),"")

OR share the Excel file with dummy data (having the non-breaking spaces in it) to try.

 

[Fluff]

How exactly is that meant to work?
Also what makes you think there are non-breaking spaces?
What the OP is getting is typical of converting a csv into Excel.

 

[Sufiyan97]

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.

Edit:
IGNORE
I did not see @Alex Blakenburg post before posting

What is your computer date and time? is it dd/mm/yyyy?

It looks your excel dates are mm/dd/yyyy.

 

[proficient]

How exactly is that meant to work?
Also what makes you think there are non-breaking spaces?
What the OP is getting is typical of converting a csv into E

It's a wild guess, and I also asked him to share the dummy data.

Typically, we face non-breaking space issues after importing data from the software.

 

[mswaf01]

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

I've realised I actually need something else - I need the date and time, but with only the hour (excluding mins and secs).

I found an additional column with the date and hour which I used to extract the hour using the RIGHT function, however this column is text rather than a true date.

I have used the DATE function to generate dates, but now I need it to also include the hour. How can I do this?

In the end, I need it to match this (column A) so I can merge the files so that the SpeedAvg and WindDir values from the below will be copied to everything that falls within the corresponding date and hour of my original file.

 

[mswaf01]

Edit:
IGNORE
I did not see @Alex Blakenburg post before posting

What is your computer date and time? is it dd/mm/yyyy?

It looks your excel dates are mm/dd/yyyy.

mm/dd/yyyy

 

[Fluff]

I need the date and time, but with only the hour

In that case just convert all the cells to proper dates & format them to not show the minutes & seconds (although they will still be there)

 

[Alex Blakenburg]

Assuming the import gives you Date Hour in column A (which looks nothing your original example).
You could try something like this:

20230807 Date Conversion mswaf01.xlsx
	A	B
1	Orig Date	Date
2	2020-11-01 13	1/11/2020 13:00
3	2020-11-21 11	21/11/2020 11:00
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=DATEVALUE(LEFT(A2,10))+TIME(--RIGHT(A2,2),0,0)

 

[mswaf01]

In that case just convert all the cells to proper dates & format them to not show the minutes & seconds (although they will still be there)

Assuming the import gives you Date Hour in column A (which looks nothing your original example).
You could try something like this:

20230807 Date Conversion mswaf01.xlsx
	A	B
1	Orig Date	Date
2	2020-11-01 13	1/11/2020 13:00
3	2020-11-21 11	21/11/2020 11:00
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=DATEVALUE(LEFT(A2,10))+TIME(--RIGHT(A2,2),0,0)

I've got it now, thanks! I had to create a TIME using dummy values of 0 for minutes and second, then =DATE column + TIME column, then re-format to dd/mm/yyyy hh:mm
Merge successful.