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

 

[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