# Thread: calculating workdays from an exit date (Excel 2003 sp2)

1. ## calculating workdays from an exit date (Excel 2003 sp2)

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

2. ## Re: calculating workdays from an exit date (Excel 2003 sp2)

Post deleted by LegareColeman

3. ## Re: calculating workdays from an exit date (Excel 2003 sp2)

This calculates beyond the 60 workdays for the first review.

4. ## Re: calculating workdays from an exit date (Excel

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.

5. ## Re: calculating workdays from an exit date (Excel

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

6. ## Re: calculating workdays from an exit date (Excel

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

7. ## Re: calculating workdays from an exit date (Excel

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.

8. ## Re: calculating workdays from an exit date (Excel

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

9. ## Re: calculating workdays from an exit date (Excel

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

10. ## Re: calculating workdays from an exit date (Excel

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.

11. ## Re: calculating workdays from an exit date (Excel

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?

12. ## Re: calculating workdays from an exit date (Excel 2003 sp2)

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.

13. ## Re: calculating workdays from an exit date (Excel 2003 sp2)

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.

<code>
=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)
</code>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•