Placing Date Portion First And Then Time From Given Data

[Ashfaque Hussain]

Hi,

I have following date data received from a website. I collected these dates in Excel. Basically this is Immigration Entries at the airport that show a person has immigrated on so n so date and time. But airlines date format is typical like below.

00:29:19*2008-12-25
14:25:20*2009-09-17
21:05:00*2011-12-26
03:18:20*2014-01-17
16:13:13*2016-09-26
23:00:40*2017-03-11
19:23:22*2018-04-26
16:29:51*2018-11-10
21:01:23*2021-04-28

I need to appear this data in following manner so that I can utilize it for further process.

Monday 15:23 20/08/2018 15:23
Friday 14:16 19/02/2021 14:16
Saturday 01:11 31/12/2022 01:11

I want the date portion to appear first with / (not with - sign) and then time portion so I can display day and time of the entry in other column - as shown above.

Is there any function or code to do it?

Regards,
Ashfaque Hussain

[sweep]

Hi,

To extract the date, use this..

=DATEVALUE(RIGHT(A1,10))

To extract the time, use this...

=TIMEVALUE(LEFT(A1,8))

Apply the required format from the ribbon: Home > Number > Select from drop down list

[CheeseSandwich]

If you are running Excel 365 then maybe the below, if you are not running 365 then the below should help anyway:

Please Login or Register  to view this content.

[TMS]

Similar approach, if you have 365:

Please Login or Register  to view this content.

This, as the previous solution, will output text values. If you want to be able to filter on the numeric date and time values, use this:

Please Login or Register  to view this content.

and use a Custom format:

Please Login or Register  to view this content.

[romperstomper]

Or:

=MID(A1&" "&A1,FIND("*",A1)+1,LEN(A1))+0

then format however. you like.

[Rick Rothstein]

If you are running Excel 365 then maybe the below, if you are not running 365 then the below should help anyway:

Please Login or Register  to view this content.

I don't think that the OP wanted all that information in a single column of cells (I think the responses by 'sweep' and 'rorya' are more on the right track) BUT if he did, this much shorter formula would produce the same output as the formula you posted...

Please Login or Register  to view this content.

[Rick Rothstein]

=MID(A1&" "&A1,FIND("*",A1)+1,LEN(A1))+0

then format however. you like.

Since the format of the original data is fixed in size, your formula can be shortened to this...

=0+MID(A1:A9&" "&A1:A9,10,19)

[CheeseSandwich]

this much shorter formula would produce the same output as the formula you posted...

Please Login or Register  to view this content.

Thanks for that, i could have made it shorter but wanted to leave it so the OP could see how i had seperated each part as i did not know if the OP wanted a string or seperated values. A screenshot of the result would have made it clearer. The formula was created so as it could be broken down into segments and used that way, more like Sweep's approach.