CODEWITHSUNDEEP
excelvbafunctiondatesubtraction
mojo3340
mojo3340

Reputation: 549

subtracting integer from a date format VBA

Below is the code i have developed. The objective is to subtract "1" or "2" from the data, depending on if is a saturday or a sunday, respectively. Column 3 contains the dates in short date format, column 5 contains the same dates but converted to "dddd" format.

The error occurs within the for loop at the line

if cells(i,5).Value = "Sat" Then

The interesting thing here is that the code runs without an "error" per say, it just skips over the if statement complelety, at that line.

Sub DataChange()
    Dim i
    Dim n
    n = Cells(Rows.Count, 3).End(xlUp).Row
    For i = 4 to n
        If Cells(i,5).Value = "Sat" Then
            Cells(i,3).Value = Cells(i,3).Value - 1
        End If
        If Cells(i,5).Value = "Sun" Then
            Cells(i,3).Value = Cells(i,3).Value - 2
        End If
    Next i
End Sub

Upvotes: 2

Views: 277

Answers (2)

scossa
scossa

Reputation: 1

column 5 contains the same dates but converted to "dddd" format

"dddd" exposes "Saturday" not "Sat" that is exposed with "ddd".

Still try to change Cells(i,5).Value with Cells(i,5).Text:

If Cells(i,5).Text = "Sat" Then ....

or

If Cells(i,5).Text = "Saturday" Then ....

Upvotes: 0

SierraOscar
SierraOscar

Reputation: 17637

Just replace it all with:

For i = 1 To Cells(Rows.Count, 3).End(xlUp).Row
    With Cells(i, 3)
        .Value = .Value - IIf(Weekday(.Value, vbSaturday) < 3, Weekday(.Value, vbSaturday), 0)
    End With
Next

The Weekday() method returns an integer representing a day of the week, based on the start day that you provide. Weekday(.Value, vbSaturday) will return 1 for Saturday, and 2 for Sunday. Combined with an immediate If IIf() we can check that the value returned is less than 3 (e.g. 1 or 2) and if it is, subtract that from the date in column C

Upvotes: 1

Related Questions