| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Payment Days and Date
Good morning all,
I would like to calculate the date and day of the following please. If a loan was to be taken out for 96 months with one payment per month starting with the first payment on say the 18-03-2011 which is in cell "D1", what formula would I use to give me the date of the other 95 months BUT exclude Saturdays and Sundays, so basically, the payment date would be on or around the 18th of each month or a day or two days before if these turn out to be Saturdays or Sundays. I will put the formula in column "D2:D96". I did try using a sum like =D2+365/12 but it returns Saturdays and Sundays. The formula I am using to find out what particular day it is, is =TEXT(WEEKDAY(D1), "dddd"). Thanks in advance. Kind regards, Paul |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate Payment Days and Date
On Thu, 17 Mar 2011 03:50:08 -0700 (PDT), Paul Black wrote:
Good morning all, I would like to calculate the date and day of the following please. If a loan was to be taken out for 96 months with one payment per month starting with the first payment on say the 18-03-2011 which is in cell "D1", what formula would I use to give me the date of the other 95 months BUT exclude Saturdays and Sundays, so basically, the payment date would be on or around the 18th of each month or a day or two days before if these turn out to be Saturdays or Sundays. I will put the formula in column "D2:D96". I did try using a sum like =D2+365/12 but it returns Saturdays and Sundays. The formula I am using to find out what particular day it is, is =TEXT(WEEKDAY(D1), "dddd"). Thanks in advance. Kind regards, Paul 1. To see the day of the week, merely format the cell with the date to include it. e.g. Format/Number/Custom Type: dddd, mmmm dd, yyyy (Or select "long date format") 2. To calculate your pay date, using the preceding Friday if the date falls on a weekend, use: D2: =WORKDAY(DATE(YEAR($D$1),MONTH($D$1)+ROWS($1:1),DA Y($D$1))+1,-1) and fill down to D96 a. If you are using a version of Excel prior to 2007, and this formula returns the #NAME error, see HELP for the WORKDAY function for instructions on installing the Analysis ToolPak. b. Note that this function has an optional Holidays argument which can be used to also alter the date if the paydate might fall on a Holiday. e.g. With Holiday Dates in J1:J10, you could use: =WORKDAY(DATE(YEAR($D$1),MONTH($D$1)+ROWS($1:1),DA Y($D$1))+1,-1,$J$1:$J$10) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate Payment Days and Date
On Mar 17, 11:36*am, Ron Rosenfeld wrote:
On Thu, 17 Mar 2011 03:50:08 -0700 (PDT), Paul Black wrote: Good morning all, I would like to calculate the date and day of the following please. If a loan was to be taken out for 96 months with one payment per month starting with the first payment on say the 18-03-2011 which is in cell "D1", what formula would I use to give me the date of the other 95 months BUT exclude Saturdays and Sundays, so basically, the payment date would be on or around the 18th of each month or a day or two days before if these turn out to be Saturdays or Sundays. I will put the formula in column "D2:D96". I did try using a sum like =D2+365/12 but it returns Saturdays and Sundays. The formula I am using to find out what particular day it is, is =TEXT(WEEKDAY(D1), "dddd"). Thanks in advance. Kind regards, Paul 1. *To see the day of the week, merely format the cell with the date to include it. * * * * e.g. *Format/Number/Custom Type: *dddd, mmmm dd, yyyy (Or select "long date format") 2. *To calculate your pay date, using the preceding Friday if the date falls on a weekend, use: D2: * * =WORKDAY(DATE(YEAR($D$1),MONTH($D$1)+ROWS($1:1),DA Y($D$1))+1,-1) * * * * and fill down to D96 * * * * a. *If you are using a version of Excel prior to 2007, and this formula returns the #NAME error, see HELP for the WORKDAY function for instructions on installing the Analysis ToolPak. * * * * b. *Note that this function has an optional Holidays argument which can be used to also alter the date if the paydate might fall on a Holiday. e.g. *With Holiday Dates in J1:J10, you could use: * * * * =WORKDAY(DATE(YEAR($D$1),MONTH($D$1)+ROWS($1:1),DA Y($D$1))+1,-1,$J$1:$J$10)- Hide quoted text - - Show quoted text - Thanks Ron, I have changed the cell "D1" to "D5" for the first date and tried to adapt the formula to start in cell "D6" accordingly but for some reason it doesn't work. This is what I am using ... =WORKDAY(DATE(YEAR($D6),MONTH($D6)+ROWS(6:6),DAY($ D6))+5,-5) Kind regards, Paul |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate Payment Days and Date
Maybe try this:
=DATE(YEAR(D1),MONTH(D1)+1,DAY($D$1))+ (WEEKDAY(DATE(YEAR(D1),MONTH(D1)+1,DAY($D$1)),2)5 )*(5- WEEKDAY(DATE(YEAR(D1),MONTH(D1)+1,DAY($D$1)),2)) On Mar 17, 11:50*am, Paul Black wrote: On Mar 17, 11:36*am, Ron Rosenfeld wrote: On Thu, 17 Mar 2011 03:50:08 -0700 (PDT), Paul Black wrote: Good morning all, I would like to calculate the date and day of the following please. If a loan was to be taken out for 96 months with one payment per month starting with the first payment on say the 18-03-2011 which is in cell "D1", what formula would I use to give me the date of the other 95 months BUT exclude Saturdays and Sundays, so basically, the payment date would be on or around the 18th of each month or a day or two days before if these turn out to be Saturdays or Sundays. I will put the formula in column "D2:D96". I did try using a sum like =D2+365/12 but it returns Saturdays and Sundays. The formula I am using to find out what particular day it is, is =TEXT(WEEKDAY(D1), "dddd"). Thanks in advance. Kind regards, Paul 1. *To see the day of the week, merely format the cell with the date to include it. * * * * e.g. *Format/Number/Custom Type: *dddd, mmmm dd, yyyy (Or select "long date format") 2. *To calculate your pay date, using the preceding Friday if the date falls on a weekend, use: D2: * * =WORKDAY(DATE(YEAR($D$1),MONTH($D$1)+ROWS($1:1),DA Y($D$1))+1,-1) * * * * and fill down to D96 * * * * a. *If you are using a version of Excel prior to 2007, and this formula returns the #NAME error, see HELP for the WORKDAY function for instructions on installing the Analysis ToolPak. * * * * b. *Note that this function has an optional Holidays argument which can be used to also alter the date if the paydate might fall on a Holiday. e.g. *With Holiday Dates in J1:J10, you could use: * * * * =WORKDAY(DATE(YEAR($D$1),MONTH($D$1)+ROWS($1:1),DA Y($D$1))+1,-1,$J$1:$J$10)*- Hide quoted text - - Show quoted text - Thanks Ron, I have changed the cell "D1" to "D5" for the first date and tried to adapt the formula to start in cell "D6" accordingly but for some reason it doesn't work. This is what I am using ... =WORKDAY(DATE(YEAR($D6),MONTH($D6)+ROWS(6:6),DAY($ D6))+5,-5) Kind regards, Paul- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate Payment Days and Date
On Mar 17, 11:54*am, AB wrote:
Maybe try this: =DATE(YEAR(D1),MONTH(D1)+1,DAY($D$1))+ (WEEKDAY(DATE(YEAR(D1),MONTH(D1)+1,DAY($D$1)),2)5 )*(5- WEEKDAY(DATE(YEAR(D1),MONTH(D1)+1,DAY($D$1)),2)) On Mar 17, 11:50*am, Paul Black wrote: On Mar 17, 11:36*am, Ron Rosenfeld wrote: On Thu, 17 Mar 2011 03:50:08 -0700 (PDT), Paul Black wrote: Good morning all, I would like to calculate the date and day of the following please. If a loan was to be taken out for 96 months with one payment per month starting with the first payment on say the 18-03-2011 which is in cell "D1", what formula would I use to give me the date of the other 95 months BUT exclude Saturdays and Sundays, so basically, the payment date would be on or around the 18th of each month or a day or two days before if these turn out to be Saturdays or Sundays. I will put the formula in column "D2:D96". I did try using a sum like =D2+365/12 but it returns Saturdays and Sundays. The formula I am using to find out what particular day it is, is =TEXT(WEEKDAY(D1), "dddd"). Thanks in advance. Kind regards, Paul 1. *To see the day of the week, merely format the cell with the date to include it. * * * * e.g. *Format/Number/Custom Type: *dddd, mmmm dd, yyyy (Or select "long date format") 2. *To calculate your pay date, using the preceding Friday if the date falls on a weekend, use: D2: * * =WORKDAY(DATE(YEAR($D$1),MONTH($D$1)+ROWS($1:1),DA Y($D$1))+1,-1) * * * * and fill down to D96 * * * * a. *If you are using a version of Excel prior to 2007, and this formula returns the #NAME error, see HELP for the WORKDAY function for instructions on installing the Analysis ToolPak. * * * * b. *Note that this function has an optional Holidays argument which can be used to also alter the date if the paydate might fall on a Holiday. e.g. *With Holiday Dates in J1:J10, you could use: * * * * =WORKDAY(DATE(YEAR($D$1),MONTH($D$1)+ROWS($1:1),DA Y($D$1))+1,-1,$J$1:$J$10)**- Hide quoted text - - Show quoted text - Thanks Ron, I have changed the cell "D1" to "D5" for the first date and tried to adapt the formula to start in cell "D6" accordingly but for some reason it doesn't work. This is what I am using ... =WORKDAY(DATE(YEAR($D6),MONTH($D6)+ROWS(6:6),DAY($ D6))+5,-5) Kind regards, Paul- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - Thanks to BOTH of you, I now have a working formula. Kind regards, Paul |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate Payment Days and Date
On Thu, 17 Mar 2011 04:50:47 -0700 (PDT), Paul Black wrote:
I have changed the cell "D1" to "D5" for the first date and tried to adapt the formula to start in cell "D6" accordingly but for some reason it doesn't work. This is what I am using ... =WORKDAY(DATE(YEAR($D6),MONTH($D6)+ROWS(6:6),DAY( $D6))+5,-5) Your adaptation is incorrect. You only need to change the cell reference to your First payment date and NOT anything else: =WORKDAY(DATE(YEAR($D$5),MONTH($D$5)+ROWS($1:1),DA Y($D$5))+1,-1) |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate Payment Days and Date
On Thu, 17 Mar 2011 04:54:55 -0700 (PDT), AB wrote:
Maybe try this: =DATE(YEAR(D1),MONTH(D1)+1,DAY($D$1))+ (WEEKDAY(DATE(YEAR(D1),MONTH(D1)+1,DAY($D$1)),2) 5)*(5- WEEKDAY(DATE(YEAR(D1),MONTH(D1)+1,DAY($D$1)),2) ) With D1: 31 May 2011 D2: 01 July 2011 D3: 31 Aug 2011 In other words, your formula skips almost a month! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate Payment Days and Date
On Thu, 17 Mar 2011 04:50:47 -0700 (PDT), Paul Black wrote:
Thanks Ron, I have changed the cell "D1" to "D5" for the first date and tried to adapt the formula to start in cell "D6" accordingly but for some reason it doesn't work. This is what I am using ... =WORKDAY(DATE(YEAR($D6),MONTH($D6)+ROWS(6:6),DAY( $D6))+5,-5) Paul, In addition to my previous post, I neglected to ask you how you want to treat a start date such as 31 May 2011? Is the next payment due on 30 Jun or on 1 Jul? Assuming it will be due on 30 June, I would suggest this much shorter formula: =WORKDAY(EDATE($D$1,ROWS($1:1))+1,-1) $D$1 refers to the cell containing the FIRST date. That is all you need to change if you move that to another cell. e.g. with D5 as the first date: =WORKDAY(EDATE($D$5,ROWS($1:1))+1,-1) Again, there is the optional Holidays argument that can be used. By the way, AB's suggestion will give incorrect dates if the start date is at the end of the month. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Working Days of Week and DUE DATE for PAYMENT | Excel Worksheet Functions | |||
| set payment date 28 days after following friday | Excel Discussion (Misc queries) | |||
| Auto calculate for date + days forward to yield new date | Excel Worksheet Functions | |||
| formula to calculate future date from date in cell plus days | Excel Worksheet Functions | |||
| calculate payment with first payment due date variable? | Excel Worksheet Functions | |||
Linear Mode
