Microsoft Excel can be used to bring your attention to payments due this week. This can then be adapted to create alerts for payments due next week to efficiently track payments.

Track Payments Due this Week

Conditional Formatting in Excel can be used to bring your attention to dates, numbers or text that meets certain criteria. This makes it the perfect tool to create alerts for due payments.

Using Excel 2007

1.  Select the cells containing the payment dates
2.  Click the Home tab on the ribbon
3.  Click the Conditional Formatting button in the Styles group
4.  Select Highlight Cell Rules and then A Date Occurring
5.  Click on the drop down arrow for Yesterday and select This week

Format cells if Payments due this week

The formula above assumes that the payment dates are in column A starting from cell A1. Substitute A1 for the appropriate starting cell of the range.

The WEEKNUM function is used to find the week number in the year. The IF function compares the week number of the payment date to the week number of today’s date (the TODAY function is used to find this out). If the week numbers are equal then Conditional Formatting is applied.

Note: Excel array functions can also be used in conditional formatting rules.

6.  Click the Format button and select the formatting you want to apply

Apply a fill colour to cells with payments due

7. Click Ok

Use the following formula to find payments due next week.


Notice the +1 being used to add a week on top of the week number found for today’s date.

By understanding how Excel works with dates and the various date and time functions available you can create alerts for any type of deadline or target date.

Other Conditional Formatting options include Next Week, This Month and Next Month.

Using Excel 2003

1.  Select the cells containing the payment dates

2.  Click the Format menu and select Conditional Formatting

Conditional Formatting in Excel 2003

3.  Click the drop down arrow for Cell value is and select Formula is

4.  Type the formula =IF(WEEKNUM($A1)=WEEKNUM(TODAY()),TRUE,FALSE) in the box provided

Format dates occuring this week

6.  Select a format you want to apply from the other drop down list. Click the Custom Format option for a larger choice of formatting

7.  Click Ok