Numbers Formula Help

[abijnk]

I need help creating this formula (if it's even possible).

I am creating a new budget sheet, and am trying to automate it as much as possible. I get paid bi-weekly, and I like to budget on a per pay period case rather than month to month. What this means is that on certain budgets I have one set of bills due, on another I'll have a different set of bills due.

So, let's say I have a bill, Bill A, that is due on the 7th every month. Also assume I have one cell that contains the start date for the budget period and one that contains the end date for the period. I need to write an if statement that says:

If the 7th falls in the date range provided (i.e. If Bill A is due during this budget) put the amount due in the cell, else put zero.

Does anyone know how (if) I can do this?

Thanks

 

[Tomorrow]

Are you thinking of using Excel for this, or some programming language?

I'm thinking of some sort of if/then/else statement.

In Excel, say you have A1 as the start date for the budget period, B2 is the end date for the period, and C3 is the amount due.

The cell which will (or won't) include the amount due would look like this:

=IF(AND(TODAY()>=A1,TODAY()<=B2),C3,0)

Excel is a little squirrelly with the "and" function. Give it a try.

 

[abijnk]

Are you thinking of using Excel for this, or some programming language?

I'm going to use Numbers. ;-)

Where I am hitting the problem is with pay periods where the dates are not all contained within one month. For example April 29 - May 12. How can I give it just the day number (not the entire DATE) and still have it figure out if it falls within the range?

 

[Tomorrow]

I'm not familiar with Numbers, but is it possible to format the "date" to an integer?

The example I gave (granted, it's Excel) works independently of whether the dates are in the same month - it compares the variable TODAY() to the two dates you enter into the two cells. It wouldn't make a difference if the two boundary dates are in different months, and I imagine it doesn't matter if they're formatted into integers.

I suspect you would have to have the same format for the boundary dates as you have for the TODAY() variable, though; I haven't tested that.

And the formula is the same whether you're using TODAY() or any other date; if you're comparing a date in, say, D1, it becomes

=IF(AND(D1>=A1,D1<=B2),C3,0)

I'll email it to you if you think it will help.

 

[abijnk]

I don't want to have to hard code a date is the problem. I want to enter the starting date, say April 29, and an ending date, say May 12, and then if the 7th of the month falls in there put $100, if it doesn't put $0. Then, when the budget is updated to May 13 through May 26, and again from May 27th through June 9th I don't want to have to touch the cell. I'm cuing off of the day number, not the entire date.

 

[mysterytramp]

I think this works. I tested it through Dec. 2011 based on a pay period ending April 23, 2010.

=IF(OR(DAY(B2)<=7,DAY(B2)>(DAY(EOMONTH(B2,-1))-8)),100,0)

B2 is the first date of the pay period

100 is a dummy value for the value you're budgeting.

DAY(x) returns the day of the month, ex: May 4, 2010 returns 4
Any pay period where the day is less than 7, gets the 100

EOMONTH(x,y) provides the last day of the month, y months away. -1 gives the previous month. So if a payday is 8 days or less from the end of the month, it gets the 100

mt