Comparing dates to set font of text in Excel

Question

I put my open tasks into a spreadsheet.

I want to make any late tasks red and bold. This code also set three lines red, bold that are not late.

Because the ones that are incorrect are in the same month as current date, I am guessing that I have a data type mismatch, or something like that.

I tried CDate.

y = 2
For x = 1 To tasks.Count
    Set tsk = tasks.Item(x)
    'Fill in Data
    If Not tsk.Complete Then
        exWb.Sheets("Sheet1").Cells(y, 1) = tsk.DueDate
        exWb.Sheets("Sheet1").Cells(y, 2) = tsk.Subject
        If tsk.DueDate < Date Then
            MsgBox tsk.DueDate & "  " & Date
            'Make red bold
            exWb.Sheets("Sheet1").Cells(y, 1).Font.Bold = True
            exWb.Sheets("Sheet1").Cells(y, 1).Font.Color = RGB(255, 0, 0)
        End If
        exWb.Sheets("Sheet1").Cells(y, 3) = tsk.PercentComplete
        exWb.Sheets("Sheet1").Cells(y, 4) = tsk.Status
        y = y + 1
    End If
Next x

Welcome Rsquest! Please take some time to read the introduction to Stack Overflow https://stackoverflow.com/tour — user10186832, Jan 07 '23 at 08:47
You might want to use DateDiff to compare the dates instead of `<` https://stackoverflow.com/questions/20270928/compare-dates-in-vba — Marcucciboy2, Jan 07 '23 at 09:51
Less or greater should work correctly if the date time value is really a date. — Eugene Astafiev, Jan 08 '23 at 20:36

Eugene Astafiev · Answer 1 · 2023-01-08T20:37:49.147

In Outlook evaluates date-time values according to the time format, short date format, and long date format settings in the Regional and Language Options applet in the Windows Control Panel. In particular, Outlook evaluates time according to that specified time format without seconds. If you specify seconds in the date-time comparison string, the filter will not operate as expected.

To make sure that the date-time comparison string is formatted as Microsoft Outlook expects, use the Visual Basic for Applications Format function (or its equivalent in your programming language). The following example creates a Jet filter to find all contacts that have been modified before June 12, 2022 at 3:30 P.M local time.

Format("6/12/2022 3:30PM","General Date")

When you are sure that both operands are dates, less or greater should work correctly. Otherwise, you can format dates and use the DateDiff function which returns a long specifying the number of time intervals between two specified dates.

Also, instead of iterating over all items in the tasks folder in Outlook and checking whether a particular task is completed:

For x = 1 To tasks.Count
     Set tsk = tasks.Item(x)
     'Fill in Data
     If Not tsk.Complete Then

You need to use the Find/FindNext or Restrict methods of the Items class. They allow getting items that correspond to the specified criteria. You may read more about these methods in the article that I wrote for the technical blog:

Comparing dates to set font of text in Excel

1 Answers1