Quote:
Originally Posted by Living the Dream
Hi everyone
In this particular sheet, I have columns that are already formatted to
"dd/mm/yyyy" and contain dates that actually display as such ( although,
these dates are wrong for my purpose).
I wrote this which works, but it keeps converting the cells to
international/US and it's giving me the (you know whats).
Sub ConvertDates()
Dim mySht1 As Worksheet, mySht2 As Worksheet
Dim myRng As Range, c As Range, WED As Range
Dim cDat As String
Set mySht1 = Sheets("TMS DATA")
Set myRng = mySht1.Range("$S$6:$U$300")
Set mySht2 = Sheets("SUPPORT DATA")
Set WED = mySht2.Range("$B$2")
'adds extra day(s) as the date needs to reflect the next working day to
which I am working on, and it also covers holidays etc..
tn = Now()
cDat = Weekday(tn, vbMonday)
If (cDat = 5) Then
cDat = Format((tn + 2) + WED, "dd/mm/yyyy")
Else
cDat = Format(tn + WED, "dd/mm/yyyy")
End If
For Each c In myRng
If c < "" Then
With c
.NumberFormat = "dd/mm/yyyy"
.Value = cDat
End With
End If
Next c
End Sub
As you can clearly see, I also format the date programatically to ensure
it happens, but it doesn't... O.o
Appreciate any assistance.
Mick.
|
Hi Mick,
I have seen similar issues as well. VBA for some reason assumes the date in the mm/dd/yyyy format despite the date setting of dd/mm/yyyy setting. I, therefore, always use a function to make changes to a date. In your situation try using something like this.
Format(DateAdd("d", 2, Date), "dd/mm/yyyy")
This expression is adding 2 days to the current date and is formatting it to the specified format. I didn't get any issues with this and hope for the best for you.
Let me know if this helps.
Thank You,
Prashant