how to split "date@time" format. Please help

[Mvirone81]

Hello,
I have in column C a long list of date and time in the following format: [TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl66, width: 82"]01/05@0558[/TD]
[/TR]
</tbody>[/TABLE]
Where 01/05 is the date and 0558 are the 5:58 AM

How can I split the format in two different columns and with the correct date (mm/dd/yyyy) and time (hh:mm) notation?

Thank you so much,

MV

 

[Sektor]

01/05 is not a date - the year is missing.

 

[Jonmo1]

To get the date (it will assume the current year)
=LEFT(A1,5)+0
Format the cell in your desired Date Format

To get the time
=TEXT(RIGHT(A1,4),"00\:00")+0
Format the cell in your desired Time format

 

[Jonmo1]

01/05 is not a date - the year is missing.

The year is not required to be entered with a date in Excel. When it's omitted, the current year is assumed.

 

[Sektor]

The year is not required to be entered with a date in Excel. When it's omitted, the current year is assumed.

Did OP mention he needs current year?

 

[Mvirone81]

To get the date (it will assume the current year)
=LEFT(A1,5)+0
Format the cell in your desired Date Format

To get the time
=TEXT(RIGHT(A1,4),"00\:00")+0
Format the cell in your desired Time format

Thank you very much. Super Helpful

 

[Jonmo1]

Did OP mention he needs current year?

Nope.

I'm just clarifying that the lack of a specified year does NOT mean the given value is NOT a date.
Just following the rules of Excel (this is an Excel forum after all), if the year is omitted, then current is assumed.

Just think like a conversation with a friend.
If you said "Hey, lets meet on January 20th"
Would you expect your friend to say "Which year?" ?

 

[Jonmo1]

Thank you very much. Super Helpful

You're welcome.

 

[Sektor]

@Jonmo1
What's the connection between what Excel interprets and what OP needs? What if his workbook is sales workbook of 2006? Then all dates are of 2006. See the idea?

 

[Sektor]

@Jonmo1
The year can be interpreted only from context - and Excel is not a judge.