Excel workday commend and all the results are false-CodePudding

I have 2 years data, and i want to know which day is working day. Some of the data is shown below

13/6/2019   0.125   0.08625 0.325243    0.086549    0.227958
14/6/2019   0.166667    0.129986    0.333958    0.091333    0.222882
15/6/2019   0.125   0.089597    0.205069    0.010063    0.138368
16/6/2019   0.125   0.047264    0.238396    0.078625    0.06
17/6/2019   0.166667    0.086486    0.325958    0.088458    0.223771
18/6/2019   0.125   0.09125 0.411299    0.094   0.260806
19/6/2019   0.166667    0.09775 0.346493    0.092326    0.245431
20/6/2019   0.125   0.096833    0.344306    0.094542    0.240028
21/6/2019   0.166667    0.06125 0.312299    0.079965    0.209965
22/6/2019   0.125   0.076667    0.304125    0.076542    0.156271
23/6/2019   0.125   0.007083    0.187125    0.008875    0.114563
24/6/2019   0.159722    0.090674    0.337708    0.094097    0.232764

Then I add a column and use commend workday to check. However, all the result is FALSE. I think it is impossible, because I already change those cells format into date, and it is still FALSE.

The most interesting is, if I just double left click the cell and change nothing, then press enter. The workday become TRUE! Why???? Originally just like that, the date is in left hand side of the cell

After I edit and press enter, it becomes right hand side and become TRUE.

Since I have 2 years data, it is impossible for me to edit them 1 by 1. Is there any solution???

CodePudding user response：

For these cases, i usually use this macro:

Sub SubDate()
    
    Dim RngDate as Range
    Dim StrDate As String
    
    Set RngDate = Range("A2")
        
    Do Until RngDate.Value = ""
        
        StrDate = RngDate.Value
        RngDate.Value = CDate(StrDate)
        Set RngDate = RngDate.Offset(1, 0)
        
    Loop
    
End Sub

A formula based solution for your case might be this one:

=DATE(RIGHT(A2,4),MID(LEFT(A2,FIND("/",A2,FIND("/",A2) 1)-1),FIND("/",A2) 1,2),LEFT(A2,FIND("/",A2)-1))