Excel, VBA and Dates

1
votes

All I am trying to do is take a date, add one to the date, stick it in a worksheet and let the worksheet run a formulae on the entered date. The cells are all formatted as English dates but when I assign the cell's value with the below code it thinks the date it's being given is in American format even though it's already in English format and so tries to convert it to English format. How can I stop it from thinking in American?

I abandoned all VBA's functions and made a function to add one to the date because VBA was just giving me a headache.

Dim D, M, Y As String
    Dim S1, S2, NewDay, NewMonth, NewYear As Integer
    S1 = InStr(1, StartDate.Value, "/")
    S2 = InStr(S1 + 1, StartDate.Value, "/")
    Debug.Print (CStr(S1) + " " + CStr(S2))
    D = Mid(StartDate.Value, 1, S1 - 1)
    M = Mid(StartDate.Value, S1 + 1, S2 - S1 - 1)
    Y = Mid(StartDate.Value, S2 + 1)
    Debug.Print (D + " " + M + " " + Y)

    NewDay = CInt(D) + 1
    NewMonth = CInt(M)
    NewYear = CInt(Y)
    If NewDay > DaysInMonth(M, Y) Then
        NewDay = 1
        NewMonth = NewMonth + 1
        If NewMonth > 12 Then
            NewMonth = 1
            NewYear = NewYear + 1
        End If
    End If

    StartDate.Value = CStr(NewDay) + "/" + CStr(NewMonth) + "/" + CStr(NewYear)

then in another function I just set the cell's value like so

With sheet.Range("A2")
'.. Stuff    
.Offset(i, 1) = StartDate.Value
4
excelvbadate
Are the dates being stored as Text in your Excel spreadsheet or as serial dates (i.e. the underlying cell value is something like 40179 for 1 Jan 2010)? – Joel Goodwin
I have fixed everything from changing the region on my computer from United States to United Kingdom. Thank god that frustration's over. – Rarge
If you're using Excel dates (and not Text) then stema has the right answer. The only time you need to worry like this is when you're saving as CSVs and the like. Here's another example: I just created a function with one line: ActiveCell.Value = ActiveCell.Value + 1 and worked just fine on a cell containing a date. The formatting is a separate issue to do with the numeric format of the cells. You don't need to worry about that during date manipulation. – Joel Goodwin

4 Answers

2
votes

VBA expects all dates to be in American way: mm/dd/yy.
I simply use constant for the proper format: kUsFmt = "#mm\/dd\/yyyy#"
Then wherever I need a date I use the format function with the kUsFmt: 

strSql = "...WHERE myDate = " & Format (Date,kUsFmt)

edit:

And why are you splitting things in M d y in your code ? Adding 1 to a date is as simple as
myDate = myDate + 1 !

1
votes

I think that the problem lies in manipulating as a String. Why not try something like this:

StartDate.Value = Format(DateSerial(NewYear,NewMonth,NewDay), "dd/mm/yyyy")

1
votes
Function AddDay(StartDate As Date) As Date
    AddDay = StartDate + 1
End Function

Maybe I don't understand your problem, but if you treat your date as Date type, Excel will do this for you. You don't have to think about End of month or end of year.

How this will look in your Excel cell is another part. this is pure formatting. Either you format the cell directly (right click -> format cell -> select "Date" and choose your format) or in VBA you have to say the Cell Object what format to display.

Please don't do this by your own manipulating strings, this is like reinventing the wheel.

You can learn here a lot about dates in Excel cpearson.com/Excel/DateFunctions, and on Excel at all.

Update: This is the better link, my first one is on date formulas within cells.

0
votes

I know that using dates can be hugely frutstrating, but please dont write yet another date conversion / adding / formatting routine, you're future maintainers will not thank you.

About the only format that is universal is yyyy-mm-dd which will be read correctly by virtually every date function around. Try writing in this format and reading in standard British Date format.