Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Idaho, USA
    Posts
    137
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Post deleted by LegareColeman
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Idaho, USA
    Posts
    137
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    This calculates beyond the 60 workdays for the first review.

  4. #4
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Idaho, USA
    Posts
    137
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculating workdays from an exit date (Excel

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

  7. #7
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Idaho, USA
    Posts
    137
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #9
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Idaho, USA
    Posts
    137
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #11
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Legare Coleman

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>
    Legare Coleman

Posting Permissions

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