Determining Next Date with Excel
Excuse me while I self-document something. I've been using Excel for years. I've done some crazy stuff with Excel. One of the more tame things that I've done is track my finances with it. I have a spreadsheet that I've entered all my bills and especially indicated all the ones that automatically withdraw from my bank account.
However, I want to know the next time they're going to come out. I can tell you the next time something will come out whether it's monthly, bi-weekly, or weekly. And I even have conditional formatting in place to colour code when things are more urgent.
Figuring out a date by the day of the month is the easiest: just check if you've passed the current day within the month. If you have, grab the next month.
=DATE( YEAR(NOW()), MONTH(NOW()) + (IF(DAY(NOW())>x,1,0)), x)
Just replace x with the day of the month that the bill comes out on.
Weekly is the next step up in difficulty. The trick is to figure out what day of the week something will be coming out on. Using the weekday function, shift to the beginning of the week. Then, add the number of days to get to the weekday the bill comes out on. Add an additional check to see if the weekday has passed. If it has, jump ahead a week.
=DATE( YEAR(NOW()), MONTH(NOW()), DAY(NOW())) - WEEKDAY(NOW()) + (IF(WEEKDAY(NOW())>x,7,0)) + x
This time x is the day of the week, with 0 being Sunday, I believe.
Finally, biweekly is even more complicated than the weekly. You have to shift to the beginning of the week but this time, you have to figure out if you're every second week. To do this, I use the
WEEKNUM function and grab the modulo of that. So, not only do I shift to the beginning of the week, I shift back another week on top of that if it's an odd week. Then I shift forward to the correct day of the week.
=DATE( YEAR(NOW()), MONTH(NOW()), DAY(NOW())) - WEEKDAY(NOW()) - IF(MOD(WEEKNUM(NOW()),2),-7,0) + x
This time, x is the number of days from the "zero day" of a two-week period. That's a little confusing, I know. Basically, just play with the value until it sits on the proper day. It'll be a number between 0 and 14.
Then, use conditional formatting to colour highlight the days to point out which bills are more urgent. Office 2007 makes this really easy: from the Home ribbon, click on Conditional Formatting and select any of the colour scales. The red-green one (the first one on my list) is probably the best as the urgent ones will be highlighted in red with the non-urgent ones highlighted in green.