CODEWITHSUNDEEP
arraysdateexceldateaddvba
Liquidgenius
Liquidgenius

Reputation: 708

Excel, VB - Subtract Days from Date Using DateAdd()

ISSUE

I wish to subtract a 1 to 3 digit number stored in an array from a date field in the worksheet. I take the absolute value of the number, multiply it by negative 1, then use the DateAdd function to perform the operation. I receive no error messages, but the array value remains the exact 1 to 3 digit number originally sent through the process.

EXAMPLE

Column C is what my current results are giving me.

        A        B        C
1  1/8/09       54       54
2  3/3/11        1        1
3  8/1/10      132      132

CODE

If delType = "Numeric" Then
    ElseIf delChars = 3 Or delChars = 2 Or delChars = 1 Then
         del(i, 1) = Abs(del(i, 1))
         del(i, 1) = del(i, 1) * -1
         del(i, 1) = DateAdd("d", del(i, 1), Range("E" & i + 1))
         'I did confirm that this case is actually working 
         'by setting the above line to del(i,1) = "Digits" and
         'received "Digits" for all entries with 1 to 3 numeric digits.
    End If
End If

Upvotes: 0

Views: 972

Answers (4)

bonCodigo
bonCodigo

Reputation: 14361

Tim's code is actually based on the concept of A.Webb's first comment.

Given, your dates, DAYS are in A, B columns respectively and you could output the answer to column C. You could infact read both column A and B into one 2-D array or two 1-D arrays, used 1 named cell as reference point to all cells you need to interact. That will allow some cell independent/dyanmic process. Again, your issue seems rather tiny - UNLESS your have to deal with Excel Add-Ins from certain vendors who would have some bizzare impact on your data/DATES formats in Excel. I wouldn't recommend having a UDF for you. Since this is a part of a BIG operation flow, you can wrap this under a data manipulation class/module where you do all small yet important data "massaging". Also hope you pay some attention to performance of your sheet when working with DATES in large workbooks.

extremely minor suggestion:

arr(x, 1) = Abs(arr(x, 1)) - Range("B1").Offset(x - 1, 0).Value

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166196

Sub Tester()

    Dim arr, x

    arr = Range("A1:A3").Value 'values to be subtracted


    For x = 1 To UBound(arr, 1)
        ' "base" date is in A5
        arr(x, 1) = Range("A5").Value - Abs(arr(x, 1))
    Next x

    With Range("D1:D3")
        .NumberFormat = "m/d/yyyy"
        .Value = arr 'dump values back to sheet
    End With

End Sub

Upvotes: 1

SeanC
SeanC

Reputation: 15923

although your code doesn't show it, my suspicion is that the array is being treated as an integer, and you have On Error Resume Next in the code.

as the date is too big for an integer, the information is not getting replaced.

try this to see the effect

Sub test()
On Error Resume Next
Dim i As Integer
i = Now()
Debug.Print i
End Sub

with the error (non)handling in place, you get a result of 0. Without the error handling, excel stops at the i=Now() with an overflow error

Upvotes: 1

A. Webb
A. Webb

Reputation: 26446

Dates in Excel are stored as number of days since a fixed date. You can just subtract column B from column A directly in the worksheet. No VBA required. Just format the result as a date.

Upvotes: 1

Related Questions