Convert text string into an Excel date with VBA

[Harry Flashman]

I have a date range that looks like this (British date style, not US):


-- removed inline image ---


edit: I posted an image but it can't be seen for some reason:

Date Range

2010 11/10 17/10

2010 11/10 17/10

2010 11/10 17/10

2010 11/10 17/10

<colgroup><col></colgroup><tbody>
</tbody>

Thus the first cell show 11 October 2010 to 17 October 2010
I want to return just the first day (the week commencing date):


-- removed inline image ---


edit: I posted an image but it can't be seen for some reason:

Week Commencing

11/10/2010

11/10/2010

11/10/2010

11/10/2010

<colgroup><col></colgroup><tbody>
</tbody>

I have written some VBA to the best of my ability but it seems clumsy using the FORMAT function. Is there a better of doing this with VBA. I can't seem to find a function in VBA which is the equivalent of the formula function DATE.

Could someone please let me know if there is a better way of doing this? Or an alternate way of converting text to dates. Cheers.

Sub ConvertTextToBritStyleDate()
' dd/mm/yyyy (not mm/dd/yyyy)
   
' Select the cells containing the dates (not the header)
' This macro will return the text as date in the cell to the right
' so make sure there is a column available
 
Application.ScreenUpdating = False
    Dim cell As Range
    Dim MyDay As Integer, MyYear As Integer, MyMonth As Integer
    Dim MyDate As Date
    For Each cell In Selection
        MyDay = Mid(cell.Value, 6, 2)
        MyMonth = Mid(cell.Value, 9, 2)
        MyYear = Left(cell.Value, 4)
        MyDate = Format(MyYear & "/" & MyMonth & "/" & MyDay, "dd/mm/yyyy")
        cell.Offset(0, 1) = MyDate
    Next cell
   
Application.ScreenUpdating = True
End Sub

 

[JoeMo]

I have a date range that looks like this (British date style, not US):
I can't seem to find a function in VBA which is the equivalent of the formula function DATE.

The DateSerial VBA function is a good proxy for the DATE worksheet function.

DateSerial(year, month, day)
The DateSerial function syntax has these named arguments:

Part	Description
year	Required; Integer. Number between 100 and 9999, inclusive, or a numeric expression.
month	Required; Integer. Any numeric expression.
day	Required; Integer. Any numeric expression.

<tbody>
</tbody>

 

[Harry Flashman]

The DateSerial VBA function is a good proxy for the DATE worksheet function.

That is exactly what I was looking for. Thank you.

 

[Harry Flashman]

*** Disregard****
I made a typo
The code should read

MyDate = DateSerial(MyYear, MyMonth, MyDay)

**************************

I have just notice something odd about this DateSerial vba function. It returns the wrong date (out by one day): typo in the code

Take the following data

A1 = 1
B1 = 1
C1 = 2010

Option Explicit
Sub TestDateSerial()
Dim MyDate As Date
Dim MyDay As Integer
Dim MyMonth As Integer
Dim MyYear As Integer
MyDay = Range("A1").Value ' 1
MyMonth = Range("B1").Value ' 1
MyYear = Range("C1").Value ' 2010
MyDate = DateSerial(MyYear, MyMonth, MyDate)
Range("D1").Value = MyDate
End Sub

The above macro returns the date 31 December 2009, when it should return 1 January 2010. Why is that? See above