Date Calculations

[Kathryn J Bittman]

Hello,

I have a couple of problems that I need help with:

1) I have gross amounts ($) being paid back monthly @ x$ and I need to have
it calculate for the month/year the payments will end. This has no interest
accruing. ie: 32,000 @ 33.00 per month to end MM/YY.

2) I need to input a date then have an automatic calculation for 90 days in
the future and must include the day of the week. Or I need it to calculate
no more than 90 days, but display a workday using only Mon, Tues, Weds,
Thurs.

Any help will greatly be appreciated.

 

[Biff]

Hi!

1. Assume the following:

A1 = start date of pay back
A2 = 32,000
A3 = 33

=DATE(YEAR(A1),MONTH(A1)+A2/A3,DAY(A1))

Format to display in the style that you want.


2. =input_date+90

Again, format to display in the style that you want.

Or I need it to calculate no more than 90 days, but display a workday using
only Mon, Tues, Weds, Thurs.

Not sure what you mean with this.

Biff

 

[Kathryn J Bittman]

Thank you!

As to:
Or I need it to calculate no more than 90 days, but display a workday using

Not sure what you mean with this.

I have projects that must be reviewed every 90 calendar days and I need the
closest workday within the 90 days to be calculated. Now, the fly in the
ointment - I only work Mon - Thurs..not Fri.

Any suggestions?

 

[Daniel.M]

Kathryn,

I have projects that must be reviewed every 90 calendar days and I need the
closest workday within the 90 days to be calculated. Now, the fly in the
ointment - I only work Mon - Thurs..not Fri.

I am assuming for Friday, you want to have the previous Thursday. Am I right?
What's the closest working day to Saturday? Next Monday or previous Thursday?

Regards,

Daniel M.

 

[Alex Delamain]

Kathryn
See if this does what you want

=IF(WEEKDAY(A1+90)=5,+A1+89,IF(WEEKDAY(A1+90)=6,+A1+88,IF(WEEKDAY(A1+90)=7,+A1+87,+A1+90)))

It will show a date 90 days from A1 but if that date is a friday,
saturday or sunday it will show the preceeding thursday.

 

[Kathryn J Bittman]

That did the trick! I have been trying to get this right for a week.

Thank you

 

[Daniel.M]

Alex,

It does not.
You probably meant : Weekday(X,2) in place of each of your Weekday(X) calls.

Since this seems to be what Kathryn wants (but it's still different from
"closest workday within the 90 days to be calculated"), you could use the
following:

=A1+90-MAX(0,WEEKDAY(A1-2)-4)

Regards,

Daniel M.