Need help with splitting up day/moth/year hour:minute into separate columns

[EOHstudent]

I am trying to split up a Time field into separate columns for day/month/year hour/minute that are in this format: 3/3/2018 1:15. Hour:minute is military time, but Excel changes it to AM/PM automatically, which is the first problem I am having. Also, when I split the hour (1:15) part of the field, it replaces that with 0:00 in the original column, which is a problem. When I use tab as a delimiter the preview looks like what I want, but what i actually get yields 2 colums of 1/3/1900 0:00.

Help!


excel post 1.png



excel post 2.png

[AliGW]

Welcome to the forum!

Sorry, but I am not clear on what exactly you are trying to achieve - what do you want the split cells to look like?

[EOHstudent]

There should be 5 separate cells, one for day, one for month, one for year, one for hour, and one for minute. Currently it is formatted like this D/M/YYYY H:M, all in one cell (Time), so i need to split them up by either /, space, or :

The Time field H:M is in military time (13:03, 13:15, 13:30, etc) but excel keeps changing it to non-miliatary time and adding AM/PM.

[AliGW]

So why not have the same data in all five columns and use the column formatting to return what you want? Would that not work for you?

Excel 2016 (Windows) 32 bit

	B	C	D	E	F	G	H
2	03/04/2017 13:05		03	04	2017	13	05

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	D
2	=$B2

Sheet: Sheet1

Formatting:

[EOHstudent]

That is exactly what I want, but it does not work. Here is the output I get using the text to columns function:

excel post 2.png

[AliGW]

I didn't use the text to columns function.

[Pirvu Vali]

or you can to use formula if format of cell is Date. in B1 you put = Day(A2), in C1 = Month(A2), D1 - =Year(A2), E1- =Hour(A2), F1 - = Minute(A2)

[AliGW]

Yes, the formula option is another way to do it:

Excel 2016 (Windows) 32 bit

	D	E	F	G	H
3	=DAY(B2)	=MONTH(B2)	=YEAR(B2)	=HOUR(B2)	=MINUTE(B2)

Sheet: Sheet1

[EOHstudent]

Yes! that worked, thank you so much!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.