Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First Half / Second Half Month Budget
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First Half / Second Half Month Budget
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kerri" wrote: 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 todays date were the 1st till the 14th, I would like Total Due this period to show $371.00 If todays 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First Half / Second Half Month Budget
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First Half / Second Half Month Budget
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(B2 9),MONTH(B29),1))*(B22:B26<=DATE(YEAR(B29),MONTH(B 29),15))*(C22:C26)),SUMPRODUCT((B22:B26=DATE(YEAR (B29),MONTH(B29),16))*(B22:B26<=EOMONTH(B29,0))*(C 22:C26))) Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Kerri" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare 1st Half & 2nd Half of 2008. | Excel Worksheet Functions | |||
print half of rows on left and other half on right | Excel Discussion (Misc queries) | |||
Split month in half | Excel Discussion (Misc queries) | |||
Split by half month | Excel Discussion (Misc queries) | |||
Split by half month | Excel Discussion (Misc queries) |