Harry Flashman
Active Member
- Joined
- May 1, 2011
- Messages
- 361
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:
<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:
<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.
-- 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.
Code:
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
Last edited: