Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help to create biweekly dates excluding nonworking days
I have a loan with biweekly payments. An insurance charge (Loan Protection
Plan) is added to the principal the last day of the month. If this date is a weekend or holiday, the payment may be shifted back to the last working day, or forward to the next working day. I need to create a column of dates that: 1. enables biweekly or weekly dates, at my choice, beginning on the date of my choice. (I can already do biweekly) Biweekly dates must not be every 12th or every 15th, for instance, but every Monday or every Wednesday, for instance. (I can already do this) Dates must be shifted if they fall on a holiday. (I cannot do this currently) 2. allows 2 to 3 biweekly dates per month. (I can already do this) 3. allows the end of the month to be added to the other 2 -3 dates in sequential order. (I can already do this) 4. creates only one single date where the EOM falls on one of the other scheduled dates. (I can already do this) 5. shifts an EOM, when it falls on a weekend, to the previous last working day or to the next working day, at my choice. 6. combines all of the above into one formula that creates a column of dates that can be either weekly or biweekly, with EOM added, where no biweekly payment nor EOM payment falls on a weekend or holiday, where the EOM date does not duplicate another scheduled date, and where I can use reference cell values to select weekly or biweekly payments, or shift to future or past working dates. If I could figure out how to do 5 and combine it with the rest, I would have 6. My formula for 1+2+3+4 is (from somewhere in the date sequence - not the first date - does NOT adjust for holidays:): =IF(AND(Pay_Num<"",B33=EOMONTH(B33,0)),IF(B32+14< EOMONTH(B33,0),B32+14,B33+14),IF(MONTH(B33)=MONTH (B33+14),B33+14,EOMONTH(B33,0))) where Pay_Num = the Named Range of the column of payment numbers (and Pay_Date = the Named Range of the column of dates I am trying to create) I have approached the WORKDAY function from numerous angles and it will not perform 5. The WORKDAY function is mislabeled by MS and unfortunately misses the opportunity to be a great function and fill a gaping hole €“ if only it would perform exactly as defined. I have received help from the community on the two separate problems, but not on the complete solution. All responses were helpful and gave me some things to experiment with, but no joy. I am not trying to solve a business problem, although this is for a personal loan for a startup business. I am trying to learn Excel and create a very flexible amortization schedule for personal use. Any help will be appreciated. -- staplers |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help to create biweekly dates excluding nonworking days
Hi,
I'm confused - "Biweekly dates must not be every 12th or every 15th, for instance, but every Monday or every Wednesday, for instance." Since weekly or bi weekly can never fall on the same day of every month, what does this statement mean? Also, if Monday is the 12th or Wednesday is the 15th in one month what's going on? And of course "If this date is a weekend or holiday, the payment may be shifted back to the last working day, or forward to the next working day." doesn't tell us what you want to do here. Suppose the holiday is Monday and the date falls on Sunday, which way do you go? I could continue, but instead why not go back and post a clear, complete and concise question. The devil is in the details. Regarding switching between bi-weekly and weekly try this basic idea =G1+IF(C2="Bi",14,7) Enter the Start date in G1, enter the text Bi or Weekly in C2, then enter this formula in G2 and copy it down. When you change the word in C2 you will switch from bi-weekly to weekly date spacing in column G. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "staplers" wrote: I have a loan with biweekly payments. An insurance charge (Loan Protection Plan) is added to the principal the last day of the month. If this date is a weekend or holiday, the payment may be shifted back to the last working day, or forward to the next working day. I need to create a column of dates that: 1. enables biweekly or weekly dates, at my choice, beginning on the date of my choice. (I can already do biweekly) Biweekly dates must not be every 12th or every 15th, for instance, but every Monday or every Wednesday, for instance. (I can already do this) Dates must be shifted if they fall on a holiday. (I cannot do this currently) 2. allows 2 to 3 biweekly dates per month. (I can already do this) 3. allows the end of the month to be added to the other 2 -3 dates in sequential order. (I can already do this) 4. creates only one single date where the EOM falls on one of the other scheduled dates. (I can already do this) 5. shifts an EOM, when it falls on a weekend, to the previous last working day or to the next working day, at my choice. 6. combines all of the above into one formula that creates a column of dates that can be either weekly or biweekly, with EOM added, where no biweekly payment nor EOM payment falls on a weekend or holiday, where the EOM date does not duplicate another scheduled date, and where I can use reference cell values to select weekly or biweekly payments, or shift to future or past working dates. If I could figure out how to do 5 and combine it with the rest, I would have 6. My formula for 1+2+3+4 is (from somewhere in the date sequence - not the first date - does NOT adjust for holidays:): =IF(AND(Pay_Num<"",B33=EOMONTH(B33,0)),IF(B32+14< EOMONTH(B33,0),B32+14,B33+14),IF(MONTH(B33)=MONTH (B33+14),B33+14,EOMONTH(B33,0))) where Pay_Num = the Named Range of the column of payment numbers (and Pay_Date = the Named Range of the column of dates I am trying to create) I have approached the WORKDAY function from numerous angles and it will not perform 5. The WORKDAY function is mislabeled by MS and unfortunately misses the opportunity to be a great function and fill a gaping hole €“ if only it would perform exactly as defined. I have received help from the community on the two separate problems, but not on the complete solution. All responses were helpful and gave me some things to experiment with, but no joy. I am not trying to solve a business problem, although this is for a personal loan for a startup business. I am trying to learn Excel and create a very flexible amortization schedule for personal use. Any help will be appreciated. -- staplers |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help to create biweekly dates excluding nonworking days
All the days and dates mentioned previously had no reference to a real
calendar, they were just to illustrate points. Nothing said that every Monday would be on the 12th or every Wednesday would be on the 15th. It said that I wanted the biweekly dates to be a fixed day (Monday in my specific case), but not a fixed date such as 12th or 15th. I saw one biweekly calendar that did this. Definitions: working days - non-weekend and non-holiday non-working days - weekends and holidays weekends - Saturday and Sunday holidays - defined/named range called Holidays working days and non-working days are completely exclusive of one another A.The Bank's only two loan requirements: 1. one payment per month on the loan 2. one insurance premium payment per month, on "the last day of the month" B.My only two requirements (and one additional preference further down in the discussion): 1. make loan payments every other week (on Monday in this case) 2. make one insurance premium payment on the last day of the month this amounts to 26 loan payments and 12 insurance payments, 3 payments per month for 10 months and 4 payments per month for two months C.Non-working day requirements: All other requirements depend upon the calendar and upon the bank working days (and one additional preference further down in the discussion): 1. any loan payment that falls on a holiday must be shifted to the next working day 2. any insurance premium payment that falls on a weekend or a holiday must be shifted to the next working day (non holiday). B.3. It is possible that the day of the week selected for loan payment will also fall on the last day of the month (August 31 this year) and I prefer to not have two days created. I prefer for the formula to create a single date for these two events. C.3. I am assuming that the bank will shift loan or insurance premium payments to the NEXT working day any time there is a conflict with a non-working day, since this occurred on the holiday Monday, May 25. The bank shifted this to Tuesday, May 26. However, at this time I don't know what will happen if the date falls on a weekend, so I would prefer to be able to code the formula to accept input from a named cell or fixed cell location to shift dates forward or backward. I don't see any calendar instances of it, but if an insurance premium payment falls on a weekend and shifting forward or backward falls on a holiday (non working day), it must shift further to a working day. Soapbox: I can currently do all except shift dates which is the hardest part. If the WORKDAY function worked as advertised, it would easily satisfy this requirement. WORKDAY leaves a gaping hole in the date functions by not delivering according to its definition. Commentary: Having one versatile formula to perform all the above would allow a sort of universal home spreadsheet that could follow and predict (the most important feature) almost any standard mortgage or any open-ended line-of-credit loan. I'm not into "high finance" but I think this formula would be very beneficial to me as a home user. I am sure that the formula that I included on the first post can be compacted by someone with good Excel skills, I am just a journeyman and welcome improvement(s) to the formula. There also appears to be a conflict that randomly causes my existing sequence of dates to "lose data" sometimes when the spreadsheet is opened, and it reports "=#N/A" in all the date cells. I have to retrieve a good formula from another spreadsheet and copy it to the current spreadsheet. My personal requirements are simple (B above). It gets complex considering the bank schedules and calendar conflicts. Thanks for any help. -- staplers "Shane Devenshire" wrote: Hi, I'm confused - "Biweekly dates must not be every 12th or every 15th, for instance, but every Monday or every Wednesday, for instance." Since weekly or bi weekly can never fall on the same day of every month, what does this statement mean? Also, if Monday is the 12th or Wednesday is the 15th in one month what's going on? And of course "If this date is a weekend or holiday, the payment may be shifted back to the last working day, or forward to the next working day." doesn't tell us what you want to do here. Suppose the holiday is Monday and the date falls on Sunday, which way do you go? I could continue, but instead why not go back and post a clear, complete and concise question. The devil is in the details. Regarding switching between bi-weekly and weekly try this basic idea =G1+IF(C2="Bi",14,7) Enter the Start date in G1, enter the text Bi or Weekly in C2, then enter this formula in G2 and copy it down. When you change the word in C2 you will switch from bi-weekly to weekly date spacing in column G. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "staplers" wrote: I have a loan with biweekly payments. An insurance charge (Loan Protection Plan) is added to the principal the last day of the month. If this date is a weekend or holiday, the payment may be shifted back to the last working day, or forward to the next working day. I need to create a column of dates that: 1. enables biweekly or weekly dates, at my choice, beginning on the date of my choice. (I can already do biweekly) Biweekly dates must not be every 12th or every 15th, for instance, but every Monday or every Wednesday, for instance. (I can already do this) Dates must be shifted if they fall on a holiday. (I cannot do this currently) 2. allows 2 to 3 biweekly dates per month. (I can already do this) 3. allows the end of the month to be added to the other 2 -3 dates in sequential order. (I can already do this) 4. creates only one single date where the EOM falls on one of the other scheduled dates. (I can already do this) 5. shifts an EOM, when it falls on a weekend, to the previous last working day or to the next working day, at my choice. 6. combines all of the above into one formula that creates a column of dates that can be either weekly or biweekly, with EOM added, where no biweekly payment nor EOM payment falls on a weekend or holiday, where the EOM date does not duplicate another scheduled date, and where I can use reference cell values to select weekly or biweekly payments, or shift to future or past working dates. If I could figure out how to do 5 and combine it with the rest, I would have 6. My formula for 1+2+3+4 is (from somewhere in the date sequence - not the first date - does NOT adjust for holidays:): =IF(AND(Pay_Num<"",B33=EOMONTH(B33,0)),IF(B32+14< EOMONTH(B33,0),B32+14,B33+14),IF(MONTH(B33)=MONTH (B33+14),B33+14,EOMONTH(B33,0))) where Pay_Num = the Named Range of the column of payment numbers (and Pay_Date = the Named Range of the column of dates I am trying to create) I have approached the WORKDAY function from numerous angles and it will not perform 5. The WORKDAY function is mislabeled by MS and unfortunately misses the opportunity to be a great function and fill a gaping hole €“ if only it would perform exactly as defined. I have received help from the community on the two separate problems, but not on the complete solution. All responses were helpful and gave me some things to experiment with, but no joy. I am not trying to solve a business problem, although this is for a personal loan for a startup business. I am trying to learn Excel and create a very flexible amortization schedule for personal use. Any help will be appreciated. -- staplers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add days, excluding Sunday's and Holiday's | Excel Discussion (Misc queries) | |||
Counting # of days between 2 dates excluding Fri & Sat) | Excel Worksheet Functions | |||
Counting days comparing 2 dates excluding empty cells | Excel Worksheet Functions | |||
formula to calculate # of days between dates, excluding holidays | Excel Discussion (Misc queries) | |||
Count Days excluding Sundays | Excel Worksheet Functions |