Convert date to weekday name

[smide]

Hello.


In column A (A1:A600) I have dates and time in format dd.mm.yyyy - hh:mm (ex. 04.10.2019 - 20:00 , there is one space between dd.mm.yyyy and '-' and one space between '-' and hh:mm). All those cells in column A are text cells.

I need formula to convert those "dates" into weekday name. Coverted results should be in column B (B1:B600).




example.


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]04.10.2019 - 20:00[/TD]
[TD="align: center"]Fri[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]04.10.2019 - 22:30[/TD]
[TD="align: center"]Fri[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]05.10.2019 - 16:00[/TD]
[TD="align: center"]Sat[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]06.10.2019 - 12:45[/TD]
[TD="align: center"]Sun[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]

07.10.2019 - 18:00​

[/TD]
[TD="align: center"]Mon[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]....[/TD]
[TD="align: center"]....[/TD]
[/TR]
</tbody>[/TABLE]

​

 

[steve the fish]

See if this works:

=TEXT(SUBSTITUTE(SUBSTITUTE(A1,".","/")," -",""),"ddd")

 

[ChrisFoster]

Steve just beat me to it. But that formula will work.

 

[smide]

When I use your formula on 04.10.2019 - 20:00 instead of 'Fri' I got 'Wed'...

 

[steve the fish]

Your local settings are MM/DD/YYYY? You should have said seen as your data is DD/MM/YYYY

 

[Phuoc]

Try this:

=TEXT(DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2)),"ddd")

 

[ChrisFoster]

When I use your formula on 04.10.2019 - 20:00 instead of 'Fri' I got 'Wed'...

I get 'Fri' - but 10.04.2019 is 'Wed'.

Are you UK or US?

 

[smide]

Try this:

=TEXT(DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2)),"ddd")

Works great. Thank you so much.

 

[Phuoc]

You're welcome! Glad it worked.