Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date formatting issue VB
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. |
#2
|
|||
|
|||
Quote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting issue | Excel Worksheet Functions | |||
formatting issue | Excel Programming | |||
formatting issue- date format dominates | Excel Worksheet Functions | |||
formatting issue? | Excel Discussion (Misc queries) | |||
Date formatting issue in user form | Excel Programming |