Method for auto-filling a budget with expenses by date (month and day only)

[Hellomoto]

After finding this sight, I got motivated to create my own excel 'something'; and that something is a daily budget worksheet. The worksheet looks like this: Column A lists months, Column B lists days of the month, Column C lists names of expenses, Column D lists dollar amounts, column E is the running account balance.


A B C D E

Dec 11 <blank> <blank> $1000

Dec 12 Property Tax $200 $800


Also, I have a data table listing all the expenses, due dates (two columns 1 for month and 1 for day), and dollar amount. Note that year is not included.


Columns C and D are auto-populated with IF formulas and vlookup formulas that compare the month and day columns with the data in the table. There are limitations to this.


So finally, the question is: Is there a better way to link a date to a cell, instead of using a column for month and a separate column for days? In other words, is there a better way to check the date in a cell and if that date matches the date of an expense, it will populate that cell with the relevant information, like the name of the expense?

 

[Hui]

Hellomoto

I nearly always encourage people to use straight dates for dealing with Dates.

Not seperating Days, Months or Years or saving them as Text.

You can use Cell Formatting to change the appearance and just display months if that is your goal.

If you want to assign a cost to a month I would recomend you use the 1st of the month, unless you are doing daily interest calculations that will generally suffice.

 

[kchiba]

Hellomoto,


You can sort your original data table by month and day and this should give you what you want.


You can then copy/paste the relevant infromation

 

[Hellomoto]

Hui,


The reason why I separated the month and day is because I used the Vlookup function to compare the day from column A (or B) to the day in the data table. example:


IF(ISNA(VLOOKUP(DAY(B21),$O$21:$O$29,2,FALSE)),"",VLOOKUP(DAY(B21),$O$21:$O$29,2,FALSE))


Where the Range is $O$21:$O$29, and fetch value from column 2.


To keep the day and month together, I tried using IF statements like this:


IF(AND(DAY(B21)=DAY($O$24),MONTH(DB21)=MONTH($O$24)),$P$14,"")


where the AND functions is used to require that both month and day match.

But the drawback was the number of nested IF statements allowed in Excel. So, I had to use the vlookup function instead of the IF function.


So, is there a function similar to Vlookup but enables me to compare just the month and day excluding the year?

 

[kchiba]

Hellomoto,


I would still follow Hui's advice on using dates, in this way you can build a database that can extend beyond one year.


What happens if there are two items in your list that match your Month and Date, so if you have two items on Dec 12, it will always pick the first one it finds.

This is the reason for suggeting a filter/copy.

 

[Hellomoto]

kchiba,


good point. I sort of "cheated" by forcing all the expenses to be on separate days. So the first expense would be due on Dec 12, the next expense is moved to Dec 13 (or dec 11). I felt the cheat was acceptable because there aren't that many expenses that fall on the same date or day of the month. For example, a credit card bill would be due on the 12th, but I always pay it before the due date (manually or via auto-pay). So I can pick any day between statement closing and due date date.


Also, I do want to use a single column for the dates, but haven't figured out how to create the right formula. A few minutes ago, I posted a question in Chandoo's article on Index-match regarding the ability of the Match function to match days and months in a date while excluding the year.


BTW, I know the cheat may not be ideal and that the personal expense trackers found in this blog all do a great job summarizing expenses. But after fiddling around with what I have now, a column-based cash-flow budgeting worksheet that auto-fills regular expenses and incomes, I think this approach is more practical. Only caveat that is bugging me is that I am using one column for months and one column for days.