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.

Monthly

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

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.

Bi-weekly

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.

Conditional Formatting

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.

Published November 11, 2007
Categorized as Other
Short URL: https://snook.ca/s/853

Conversation

9 Comments · RSS feed
Rob Lewis said on November 12, 2007

I don't suppose you'd be willing to share a template of your Excel finance spreadsheet with us would you? I'm interested in how people track their spending, and it sounds like your spreadsheet might be a useful reference for people wanting to use a program they're already somewhat familiar with. Thanks, Rob.

Phillip (PhillyMac) said on November 13, 2007

As a former Excel instructor - nicely done :-D

Terry Apodaca said on November 13, 2007

makes me shiver! I've had to convert Excel formulas to SQL Server Reporting Services (Reports) for the past 3 months. Though I don't mind using excel for many things, I don't like it for my finances. It was just as easy to create a C# app with a access back end for that...and has a much nicer UI.

Guess you could have used anything to track your finances though...and maybe it wasn't worth the hassle to convert what you've done for several years!

Joel said on November 20, 2007

Good to see some information on excel coming from the web guys ;)

As an engineer we use excel *all the time* as a development "platform" (proof of concept), mini-app creator, dodgy hack implementor and more.

Franksta said on December 02, 2007

Thank you for the formula's! I am with Lewis I thought I was going to see a stripped down version of your Excel finances at the end attached to see how it is done. Even though a template would be nice it is interesting to see that formula's were taught at my community college however they should have had us setup formula's in this way so we had expense sheets and money management templates leaving the class. Not just reading out of some book and doing the assignments.

If half the people knew the potential Excel had to help them with their financial situations they might be rich!

Phil said on December 06, 2007

Jonathan -

I'm with Rob and Franksta. Any chance you could share the Excel template you use for your finances? I'm a Mac switcher/slider that has become extremely frustrated with the financial software offerings for OS X, and I'm thinking now that it might just be easier to track my finances in Excel. I'm especially interested to find something that will allow me to track spending against a budget so I can save up for some new Apple toys!

Othalian said on September 05, 2008

Hi, I have just discovered your site and your "love" of Excel. I was wondering if you could assist me with a small problem. I have created a spreadsheet which has a separate page for each month. It is used at work to show when someone is on a course, holiday, etc. The spreadsheet will update when the year is changed in January and add grayed out areas for each of the weekends.

What I would like to do is add the shift for that week in between the grayed out areas even when I change the year. By that I mean weather it is a Day Shift, Evening shift or Night Shift. (Days, Evenings, Nights). Any help would be great.

Jonathan Snook said on September 05, 2008

@Othalian, if what you're saying is that you want an easy way to do formatting by day of the week, you can use the WEEKDAY function and check if it's equal to 1 or 7.

Othalian said on September 05, 2008

Hi, Thanks for the very rapid response to my query. I am not sure if I explained it very clearly.

I have the days of the week.
I have also added a columns to indicate the weekends.
Under the days I have the date, indicated by a single or double digit.
Under that for the whole week ie 5 days I would like to add the shift we are on for that week. And for it to change automatically when I update the spreadsheet each year.

I am not sure how I can use the WEEKDAY function for this.

Thanks again

Sorry, comments are closed for this post. If you have any further questions or comments, feel free to send them to me directly.

Want to learn about scaling CSS for large projects?

I'm available for full and half-day workshops on scalable CSS architecture. I can provide on-site training for your team. Interested?
Get in touch.