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.

	A	B
1	04.10.2019 - 20:00	Fri
2	04.10.2019 - 22:30	Fri
3	05.10.2019 - 16:00	Sat
4	06.10.2019 - 12:45	Sun
5	07.10.2019 - 18:00​	Mon
6	....	....

<tbody>
</tbody>

​

 

[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.