Calculate Payment Days and Date

[Paul Black]

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 "D296". 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

 

[Ron Rosenfeld]

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 "D296". 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),DAY($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),DAY($D$1))+1,-1,$J$1:$J$10)

 

[Paul Black]

1.  To see the day of the week, merely format the cell with the date toinclude 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),DAY($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 functionfor 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),DAY($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

 

[AB]

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))

 

[Paul Black]

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))







- Show quoted text -

Thanks to BOTH of you, I now have a working formula.

Kind regards,
Paul

 

[Ron Rosenfeld]

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),DAY($D$5))+1,-1)

 

[Ron Rosenfeld]

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!

 

[Ron Rosenfeld]

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.