Excel VBA - How to compare today's date in one column with a future date in a second column

Question

I have two columns in a workbook; the first has today's date, the second has a date in the future. I would like to create a VBA that compares these two columns and verifies that the future date is three business days ahead of today. If the date is not three business days ahead of today, I would like to display a warning message.

Is there a way to do this? Thanks so much.

You should try to solve your problem first and post your coding attempts. — Barranka, Jul 23 '13 at 16:45
http://msdn.microsoft.com/en-us/library/office/ff835532.aspx — SeanC, Jul 24 '13 at 15:15

score 2 · Accepted Answer · answered Jul 23 '13 at 16:45

First of all, you need to understand how does VBA "understands" a date.

Excel, Access, VBA and many MS products store dates as a double precision number; the integer part of the number is the date (number of days counted since January 1st, 1900), and the decimal portion of the number is the time (fraction of day).

This makes it easy to compare two dates: You can compare, add or substract dates just as if you were using numbers. So, if t0 and t1 are two dates (t1 >= t0), then the expression t1 - t0 will give you the difference of days.

Now... how yo count the "business days" between two dates? The format() function in VBA can help you. You can use this function to return the "day-of-week" number. Check the online help of the function: http://msdn.microsoft.com/en-us/library/office/gg251755.aspx

So... how do you put this together? Here's an example:

public function bizDaysRemaining(t0 as date, t1 as date) as String
    Dim ans As String, dayCount as Integer, n as Integer
    If t1 < t0 Then
        ans = "Warning"
    Else
        dayCount = 0
        n = 0
        While t0 + n <= t1
            if format(t0 + n, "w", vbMonday) <= 5 Then dayCount = dayCount + 1
            n = n + 1
        Wend
        If dayCount < 3 Then
            ans = "Warning"
        Else
            ans = "There are " & dayCount & " business days remaining"
        End If
    End IF
    bizDaysRemaining = ans
End Function

Hope this helps you

Thanks so much for this. I would have tried to solve my problem first and post what I came up with but I'm very much a beginner and didn't know where to start. Rather than turn out a cell in a spreadsheet, however, I'm looking for something a little different. I would like to run a Macro that tests each row for dates that are 3 business days ahead of today, and if they are anything different than 3 business days ahead, a warning message is displayed. How would I manipulate this code to be run as a Macro to execute this test? Thanks again. — user2611396, Jul 23 '13 at 18:05
@user2611396 First: If you like it, upvote it and/or accept it ;) . Second: This function does not depend on cells (or any other Excel specific stuff) to work, so you can use it wherever you like. You can write a macro to read the data, and you can use this function *inside* your macro. I suggest you look for a good on-line VBA tutorial or reference, or a good book on Excel and VBA programming (there are lots of books out there). — Barranka, Jul 23 '13 at 18:12

score 1 · Answer 2 · answered Jul 23 '13 at 17:56

1

Also, it is not required to use VBA. Excel will let you treat dates as mathmateical objects and there are multiple formula functions around dates as well.

Look at the NETWORKDAYS() function.

http://office.microsoft.com/en-us/excel-help/networkdays-HP005209190.aspx

answered Jul 23 '13 at 17:56

Alan Waage

603
4
12

Wow! Didn't know this function even existed! +1 – Barranka Jul 23 '13 at 18:13

score 0 · Answer 3 · answered Jul 23 '13 at 18:49

Try this:

Assuming A1 = First Date

A2 = Second Date

Sub xtremeexcel()

x = Cells(1, 2) - Cells(1, 1)

If x > 3 Then

  If Weekday(Cells(1, 1)) <= 2 Then

   MsgBox ("Success")

  Else

    If x > 5 Then

        MsgBox ("Success")

    Else

        MsgBox ("fail")

    End If

  End If

Else

    MsgBox ("fail")

End If


End Sub

Excel VBA - How to compare today's date in one column with a future date in a second column

3 Answers3