First Half / Second Half Month Budget

[Kerri]

I hope I am explaining this ok....

I have the bills that are due in the first half and the ones due in
the second half of the month listed in my sheet.

I am using the Today function in a cell.
I want the Total Due this period to show either the bills due on the
first or second half of the month depending on what half we are in.

What I mean is, in the example below, there is $371.00 Due in the
first Half and $608.60 due in the Second Half.

If today’s date were the 1st till the 14th, I would like Total Due
this period to show $371.00
If today’s date were the 15th till the end of the month, I would like
Total Due this period to show $608.60

Cash Flow as of mm/dd/yy
1st $371.00

18th $48.00
16th $55.29
17th $191.00
23rd $156.64
27th $157.67

Today's Date 10/12/2009 11:45

Total Due this Period $979.60

Account 1 $-
Account 2 $849.16
cash available $0.00
to be deposited $0.00
Total $849.16

Expect to receive this week. $0.00
Expect to Be Available $849.16

Balance -$130.44

 

[Luke M]

A little tweaking of your setup may be required, but here's one way.

Instead of having "1st, 18th, etc)" in your dates column, use simple numbers
(1, 18, etc). Your formula then becomes:

=SUMIF(A:A,IF(DAY(TODAY())>14,">14","<=14"),B:B)

With columns A and B containing your dates and values, respectively.

 

[Kerri]

Worked Wonderful!
Thank you!

A couple questions...

Can you tell me a way to do that if I were to type a day in rather
than using the today function?
Say it was the 10th of the month, but I wanted to look today at where
we would be on the 18th.

And one final,

The last line shows the balance, how do I make it Green if positive
and red if negative?

 

[Ashish Mathur]

Hi,

Let's say the today() function is in cell B29 and data is in range B22:C26
(with dates in B22:B26 and amount in C22:C26). In cell C29, you may use

=IF(DAY(B29)<=15,SUMPRODUCT((B22:B26>=DATE(YEAR(B29),MONTH(B29),1))*(B22:B26<=DATE(YEAR(B29),MONTH(B29),15))*(C22:C26)),SUMPRODUCT((B22:B26>=DATE(YEAR(B29),MONTH(B29),16))*(B22:B26<=EOMONTH(B29,0))*(C22:C26)))

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com