Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
quarterly, semi-annually??
Hi, new to the forum so be gentle. ok deep breath.... I need help with a spreadsheet i have drawn up to keep track of bills. In one column you can select whether the bill is monthly,quarterly,semi-annual or yearly (using a dropdown list). In the next column you can select the 1st month the bill is paid (another drop down box, date in mmm format). The next column displays whether the bill is payable this month based on todays date (either yes or no). So it looks something like this: TODAYS DATE ____________M,Q,SA,Y? ___1ST BILL MONTH ___PAYABLE THIS MONTH? BILL1 _________SA ______________MAY ______________YES BILL2 _________Q________________JULY _____________NO BILL3 _________Y________________MARCH____________ NO This last column is the one im having trouble with. I can get it to say yes if the 1st bill month matches todays month, but writing a formula that works out subsequent billing months based on the m,q,sa column and then seeing if they match todays month is a real headache. they end up being massive nested affairs of the form: =IF(OR(TEXT(I5,"mmm")=TEXT(D9,"mmm"),TEXT(DATE(YEA R(D9),MONTH(D9)+3,DAY(D9)),"mmm")=TEXT(I5,"mmm")), "yes","no") where I5 is todays date and D9 is the first bill month column. This one just sees whether today matches the bill month or the bill month +3, so you could see how +3, +6, +9 would become huge. monthly and yearly are easy to sort out in a formula, because if a bills monthly it will always be payable and if its yearly then its only going to fall on the 1st bill month no matter what. So the problem is a formula for semi annual and quarterly months. Im getting myself tied up in arrays and index,match etc so i need to take a step back and let a fresh set of eyes look at the problem. cheers for listening to my rant :) -- colonel-shoe ------------------------------------------------------------------------ colonel-shoe's Profile: http://www.excelforum.com/member.php...o&userid=34695 View this thread: http://www.excelforum.com/showthread...hreadid=544631 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
quarterly, semi-annually??
Assuming C9 contains "SA", "Q" or "Y", D9 a date, I5 today's date then try =IF(MOD(MONTH($I$5)-MONTH(D9),IF(C9="Q",3,IF(C9="Y",12,6))),"No","Yes" ) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=544631 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
quarterly, semi-annually??
To accommodate "M" also.... =IF(MOD(MONTH($I$5)-MONTH(D9),IF(C9="Q",3,IF(C9="Y",12,IF(C9="M",1,6)) )),"No","Yes" ) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=544631 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
quarterly, semi-annually??
If you change the first bill date from just a month to the actual date (you
could format it as mmmm to look the same), then just use =OR(MONTH(C2)=MONTH(TODAY()),IF(B2="SA",MONTH(TODA Y())=MONTH(DATE(YEAR(C2),M ONTH(C2)+6,1))),IF(B2="Q",MONTH(TODAY())=MONTH(DAT E(YEAR(C2),MONTH(C2)+{3,6, 9},1)))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "colonel-shoe" wrote in message ... Hi, new to the forum so be gentle. ok deep breath.... I need help with a spreadsheet i have drawn up to keep track of bills. In one column you can select whether the bill is monthly,quarterly,semi-annual or yearly (using a dropdown list). In the next column you can select the 1st month the bill is paid (another drop down box, date in mmm format). The next column displays whether the bill is payable this month based on todays date (either yes or no). So it looks something like this: TODAYS DATE ____________M,Q,SA,Y? ___1ST BILL MONTH ___PAYABLE THIS MONTH? BILL1 _________SA ______________MAY ______________YES BILL2 _________Q________________JULY _____________NO BILL3 _________Y________________MARCH____________ NO This last column is the one im having trouble with. I can get it to say yes if the 1st bill month matches todays month, but writing a formula that works out subsequent billing months based on the m,q,sa column and then seeing if they match todays month is a real headache. they end up being massive nested affairs of the form: =IF(OR(TEXT(I5,"mmm")=TEXT(D9,"mmm"),TEXT(DATE(YEA R(D9),MONTH(D9)+3,DAY(D9)) ,"mmm")=TEXT(I5,"mmm")),"yes","no") where I5 is todays date and D9 is the first bill month column. This one just sees whether today matches the bill month or the bill month +3, so you could see how +3, +6, +9 would become huge. monthly and yearly are easy to sort out in a formula, because if a bills monthly it will always be payable and if its yearly then its only going to fall on the 1st bill month no matter what. So the problem is a formula for semi annual and quarterly months. Im getting myself tied up in arrays and index,match etc so i need to take a step back and let a fresh set of eyes look at the problem. cheers for listening to my rant :) -- colonel-shoe ------------------------------------------------------------------------ colonel-shoe's Profile: http://www.excelforum.com/member.php...o&userid=34695 View this thread: http://www.excelforum.com/showthread...hreadid=544631 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
quarterly, semi-annually??
cheers for the reply guys. daddylonglegs, could you explain how your equation works, im not very familiar with the MOD function. ill mess about with both and see how i get on -- colonel-shoe ------------------------------------------------------------------------ colonel-shoe's Profile: http://www.excelforum.com/member.php...o&userid=34695 View this thread: http://www.excelforum.com/showthread...hreadid=544631 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
quarterly, semi-annually??
colonel-shoe Wrote: cheers for the reply guys. daddylonglegs, could you explain how your equation works, im not very familiar with the MOD function. ill mess about with both and see how i get on The IF function gives the second argument of the MOD function. To simplify things assume C9 is "Q" then this =IF(MOD(MONTH($I$5)-MONTH(D9),IF(C9="Q",3,IF(C9="Y",12,IF(C9="M",1,6)) )),"No","Yes" ) becomes =IF(MOD(MONTH($I$5)-MONTH(D9),3),"No","Yes" ) so whenever MOD(MONTH($I$5)-MONTH(D9),3) is zero you get "Yes", otherwise "No" Assuming MONTH($I$5) is 5 (for May) then when MONTH(D9) is 2,5,8 or 11 the MOD function is zero, thus returning "Yes" every 3 months. Look up Excel help on MOD for further information. Clearly "SA" and "Y" etc. work the same way although the second argument of MOD would be set to 6 and 12 respectively, giving you "Yes" every 6 or 12 months respectively -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=544631 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Semi Annual Counter | Excel Worksheet Functions | |||
Quarterly tax percentages | Excel Worksheet Functions | |||
List employees with hours worked, weekly, quarterly, and annually | New Users to Excel | |||
quarterly reports | Excel Discussion (Misc queries) | |||
quarterly reports | Excel Worksheet Functions |