• calculating workdays from an exit date (Excel 2003 sp2)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » calculating workdays from an exit date (Excel 2003 sp2)

    Author
    Topic
    WScreser
    AskWoody Lounger
    July 28, 2006 at 6:33 pm #434061

    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
    Viewing 2 reply threads
    Author
    Replies
    • H. Legare Coleman
      AskWoody Plus
      July 28, 2006 at 7:15 pm #1022477

      Post deleted by LegareColeman

      Reply | Quote
      • WScreser
        AskWoody Lounger
        July 28, 2006 at 7:22 pm #1022478

        This calculates beyond the 60 workdays for the first review.

        Reply | Quote
        • H. Legare Coleman
          AskWoody Plus
          July 28, 2006 at 9:48 pm #1022524

          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
    • WSJogoDoBicho
      AskWoody Lounger
      July 28, 2006 at 7:33 pm #1022482

      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
      • WScreser
        AskWoody Lounger
        July 28, 2006 at 7:39 pm #1022485

        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
        • WSHansV
          AskWoody Lounger
          July 28, 2006 at 7:53 pm #1022488

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

          Reply | Quote
          • WScreser
            AskWoody Lounger
            July 28, 2006 at 8:09 pm #1022495

            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
            • WSHansV
              AskWoody Lounger
              July 28, 2006 at 8:29 pm #1022500

              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
        • WSJogoDoBicho
          AskWoody Lounger
          July 28, 2006 at 8:05 pm #1022493

          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
          • WScreser
            AskWoody Lounger
            July 28, 2006 at 8:13 pm #1022496

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

            Reply | Quote
            • WSJogoDoBicho
              AskWoody Lounger
              July 28, 2006 at 8:32 pm #1022501

              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
    • H. Legare Coleman
      AskWoody Plus
      July 28, 2006 at 10:19 pm #1022537

      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
    Viewing 2 reply threads
    Reply To: calculating workdays from an exit date (Excel 2003 sp2)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    March 2025
    S M T W T F S
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.