Convert date to weekday name

smide

Board Regular
Joined
Dec 20, 2015
Messages
152
Office Version
2007
Platform
Windows
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.


AB
104.10.2019 - 20:00Fri
204.10.2019 - 22:30Fri
305.10.2019 - 16:00Sat
406.10.2019 - 12:45Sun
5
07.10.2019 - 18:00​
Mon
6........

<tbody>
</tbody>

 
Last edited:

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,817
Office Version
365
Platform
Windows
See if this works:

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

smide

Board Regular
Joined
Dec 20, 2015
Messages
152
Office Version
2007
Platform
Windows
When I use your formula on 04.10.2019 - 20:00 instead of 'Fri' I got 'Wed'... :confused:
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,817
Office Version
365
Platform
Windows
Your local settings are MM/DD/YYYY? You should have said seen as your data is DD/MM/YYYY
 

Phuoc

Board Regular
Joined
Apr 29, 2016
Messages
147
Try this:

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

Forum statistics

Threads
1,084,878
Messages
5,380,415
Members
401,673
Latest member
Ali Balleya

Some videos you may like

This Week's Hot Topics

Top