Convert date to serial number vba

[Godders199]

Hello, I am trying to convert all columns with dates in a workbook to serial numbers, without success, I have searched the internet with not much joy, all I have been able to try is range (“k:m”).numberformat = number, this changes most but leaves some still as dates. I can only presume there is some formatting in the data I am receiving which is stopping all dates changing

is there anyway in vba to ensure all dates are changed?

I will keep looking but any help would be appreciated.

 

[eirikdaude]

Could you give an example of what a cell in the column would look like before and after? Are you talking about e.g. 31.12.2020 -> 44196,00?

If this doesn't work for all cells when you change the numberformat, I would assume some of your dates are interpreted as text. In that case something like

Option Explicit

Sub test()
    Dim c As Range, r As Range
   
    Set r = Sheet1.Range("A1:A10") ' The range containing your dates
   
    For Each c In r
        c = CDate(c)
    Next c
   
    r.NumberFormat = "0"
End Sub

might work for some of them at least.

Note that if the reason one of the dates are interpreted as text is that someone's entered e.g. 31.09.2020 (people have done this to my spreadsheets more than once), I don't think the above will work. Then you'll have to add some error handling in deciding how invalid dates are tackled.

 

[Godders199]

Could you give an example of what a cell in the column would look like before and after? Are you talking about e.g. 31.12.2020 -> 44196,00?

If this doesn't work for all cells when you change the numberformat, I would assume some of your dates are interpreted as text. In that case something like

Option Explicit

Sub test()
    Dim c As Range, r As Range
  
    Set r = Sheet1.Range("A1:A10") ' The range containing your dates
  
    For Each c In r
        c = CDate(c)
    Next c
  
    r.NumberFormat = "0"
End Sub

might work for some of them at least.

Note that if the reason one of the dates are interpreted as text is that someone's entered e.g. 31.09.2020 (people have done this to my spreadsheets more than once), I don't think the above will work. Then you'll have to add some error handling in deciding how invalid dates are tackled.

Thanks, for help, get a run time error 13 , type mismatch on c = cdate(c).
presume this is down to how the date has been input as you say so so dim c should not be range?

 

[eirikdaude]

Thanks, for help, get a run time error 13 , type mismatch on c = cdate(c).
presume this is down to how the date has been input as you say so so dim c should not be range?

No, it likely means that excel doesn't manage to convert the value of the cell c to a date. It's what I'm talking about a bit further down the post.

You could try doing something like what I show below to get up a messagebox for each cell which it fails to handle:

Sub test()
    Dim c As Range, r As Range
   
    Set r = Sheet1.Range("A1:A10") ' The range containing your dates
    
    On Error GoTo errhandler
    For Each c In r
        c = CDate(c)
    Next c
    On Error GoTo 0
    
    r.NumberFormat = "0"
    
    Exit Sub
errhandler:
    MsgBox Prompt:="Got an error in converting cell " & c.Address & " to a date. Check the cell contents.", Title:="Error converting", Buttons:=vbExclamation
    Resume Next
End Sub

Personally I'd prefer to print this to the immediate window, so I wouldn't have to click through all the messageboxes, but I don't know how familiar you are with the VBA editor?

Finally this macro probably won't do anything more than what you already accomplished by setting the number format of the column by hand, to sum up you probably have some cells in your worksheet which you'll have to figure out why aren't parsed as dates.

 

[Godders199]

Thanks for help have got it to work now.