calculating workdays from an exit date (Excel 2003 sp2)

Topic

New Reply

I need to set a date as a reminder to review a clients progress. The reminder must be in the next quarter after the exit date. For example, if a client exits the program on 4/3/06, I need a 60, 90, 120 day reminders to followup. However, these followup dates must not be in the same quarter. If you calculate 60 dates from the exit date, you are still in the same quarter. I can’t figure out how to determine the end of a quarter, then calculate from there. Any ideas????

Reply | Quote

Replies

Post deleted by LegareColeman

Reply | Quote

This calculates beyond the 60 workdays for the first review.

Reply | Quote

Sorry, I realized the formula had a problem just as I posted it. I immediately clicked on the trash can to delete it, but didn’t click on the Yes I really want to delete the post message, and had to leave before I realized it.

Reply | Quote

If you have a date in D20, you can use the formula

=ROUNDUP(MONTH(D20)/3,0)

to get the quarter of that date.

So if there’s an exit date in D20, in the +60 date cell, you could do something like

=if(ROUNDUP(MONTH(D20)/3,0)=ROUNDUP(MONTH(D20+60)/3,0),”Same Quarter”,D20+60)

and a similar thing for the 90 day cell.

Reply | Quote

I get the text “same quarter” but no review date–should that display? I need the date to be in the next quarter.

Reply | Quote

Could you provide some examples of what exactly you want the formula(s) to return?

Reply | Quote

I need a spreadsheet that will track the dates for follow-up after clients are exited from a program. We want to plug in the exit date and have the 1st, 2nd, 3rd and 4th quarter after exit that exit date automatically calculate.
One formula I’ve tried calculates the date after a number of workdays

Reply | Quote

Could you please provide some examples of what exactly you want the result to be in various situations? Try to be specific instead of vague.

Reply | Quote

Okay, I think I see what you want.
If the exit date is in D20, you can use:

=IF(ROUNDUP(MONTH(D20)/3,0)ROUNDUP(MONTH(D20+60)/3,0),D20+60,IF(ROUNDUP(MONTH(D20)/3,0)ROUNDUP(MONTH(D20+90)/3,0),D20+90,D20+120))

To show the nearest of the +60, +90 or +120 dates that is in the next quarter.

Reply | Quote

I can’t get this to calculate—I get a formula error message about the logical test.

Reply | Quote

In the attached file, I’ve put one column with Exit Dates, another with the calculation for the nearest +60, 90 or 120 days date in the next quarter, and another in case that day is a weekend day. If this is not the sort of solution your looking for, can you attach a sample workbook, showing how you’ve laid out the data and what you’ld like to see?

Reply | Quote

OK, I am back and think I have fixed my previous problem. If you have a date in cell A1, the first formula below will give you the date of the first of the month of the next calendar quarter. The second formula gives that date plus 60 days, the third formula plus 90 days, and the last formula plus 120 days. Your subject line is calculating WORKDAYS from an exit date, but your post says nothing about workdays. If you really want to add 60 workdays, then you will need to use the WORKDAY function from the Analysis Toolpak.


=DATE(YEAR(A4),INT((MONTH(A4)-1)/3)*3+4,1)
=DATE(YEAR(A1),INT((MONTH(A1)-1)/3)*3+4,61)
=DATE(YEAR(A1),INT((MONTH(A1)-1)/3)*3+4,91)
=DATE(YEAR(A1),INT((MONTH(A1)-1)/3)*3+4,121)


Reply | Quote