Creating a Budget Calendar

Sunman · Posted: Sun Nov 04, 2007 4:09 pm Post subject: Creating a Budget Calendar

Hi All. Forgive me if this has been answered. I tried searching with various keywords but I couldn't find exactly what I'm looking for.

I'm trying to create a Calendar in Calc. I've got the basic calendar to create perpetually, based on what month it is when you open it (the idea is that I will print out a new calendar each month).

A bit of a background on this: Sheet1 has a calendar for the current month. One box is 5 cells high and 2 cells wide (let's call it A1 to B6). In A2 is the date, formatted to show the Day. Row 2 is blank. The bill name (if any) starts in A3 and the amount of the bill is in B3. If there is a second bill, it displays in A4 and B4 and so on for a maximum of 4 bills per day.

In Sheet2 I have the list of bills. Row 1 is for Day 1. Row 2 for Day 2 and so on. A1 is the bill for Day1 of the month and B1 is the amount of the bill. C1 and D1 is for the second bill (if any) and so on.. Got it so far?

What I am trying to figure out is a formula for each day to display the bill on the right day. In this example, assuming the first of the month is on the first Sunday, the formula in A3 would basically show Sheet2.A1. But what if, like in November, the first day of the month is on Thursday? Ultimately, I would like to just put =Sheet2.A(DAY(B1)) -- I need to grab the day number from the date and pass it in a variable.

The only way I've come up with to do this so far is one loooong IF statement. IF(DAY(B1)=1;Sheet2.A1;IF(DAY(B1)=2;Sheet2.A2;IF(DAY(B1)=3;Sheet2.A3 ... IF(DAY(B1)=31;Sheet2.B1)))))))))))))))))))))))))))))))

Please help. Is there a better way to do this?

I looked at a couple of Calendar programs (like Mozilla's) but I need to also run some calculations on the bills (income, outgo, difference, figure out how much to put in savings, etc) so I think I need to do this in Calc.

I'm sorry if this is confusing. I'm confused myself. Smile

davidh182 · OOo Advocate Joined: 01 Apr 2004 Posts: 413

Not sure I followed it completely, but might this help?

jwr · Posted: Mon Nov 05, 2007 5:32 am Post subject: calender concept in Calc

A German user created a nice calender in Calc.
The calender generates 1 sheet for each month.
Maybe the concept is useful to others and to be modified for a budget calender.

A download is provided at the German website:
http://www.datenschleuder.eu/get_dfbe25f383d42f63d391f8120cc27b23.html

The calender has been published under GNU LGPL Licence.

As soon as the new forum's website has been created we may provide an downloadable attachment for the calender's CALC-file at the forum's site, but unfortunately this forum does not provide any attachments.

Regards -Hans-
_________________
Windows 2K, OOo-2.3.0, MS-IE-6.0, Firefox-1.0.4, MS Office 97,
Say NO to MS OpenXML: http://www.noooxml.org/petition

Sunman · Posted: Mon Nov 05, 2007 5:39 am Post subject:

Yes! That's it perfectly. Thank you David. I knew there had to be a simple way to do it.