Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: How to calculate "first of the month after 60 days"

  1. #1
    New Member
    Join Date
    Mar 2013
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How to calculate "first of the month after 60 days"

    Hello,
    I am having trouble creating a formula to capture the "first of the month after 60 days" from the Hire Date.
    Example, Hire Date is 01/15/2013, should return 04/01/2013. Would the ROUNDUP function be useful here?
    Thanks in advance!
    - Radio

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    43,001
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to calculate "first of the month after 60 days"

    Try

    =(A1+60)-DAY(A1+60)+1
    Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    Board Regular
    Join Date
    Jul 2012
    Location
    Chicago, IL
    Posts
    1,567
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to calculate "first of the month after 60 days"

    =date(year(A1+60),month(A1+60)+1,1)

  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    43,001
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to calculate "first of the month after 60 days"

    Strike that last, misunderstood slightly..

    par's got the right answer.
    Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  5. #5
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,527
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to calculate "first of the month after 60 days"

    Here's one way with hire date in A1

    =EOMONTH(A1+59,0)+1

  6. #6
    New Member
    Join Date
    Mar 2013
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to calculate "first of the month after 60 days"

    Hi, Thanks for the quick response. The formula gave me one month prior, meaning 03/01/13 for the 01/15/2013 start date. So I tried it with 90 days, =(A1+90)-DAY(A1+90)+1 and it worked! This is cool and will help me track eligibility. Thanks for your help

  7. #7
    New Member
    Join Date
    Mar 2013
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to calculate "first of the month after 60 days"

    Hello,
    I tried all suggestions and they worked! Thanks so much. It's neat how you can solve something a few different ways, I like that about Excel.
    I really appreciate the help
    Radio

  8. #8
    Board Regular
    Join Date
    Jul 2012
    Location
    Chicago, IL
    Posts
    1,567
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to calculate "first of the month after 60 days"

    Barry's is good but as I understand these types of eligibility it really should be =EOMONTH(A1+60,0)+1

    The question is when does the eligibility start for somebody that starts on 3/2? Is it 5/1 or 6/1?

  9. #9
    New Member
    Join Date
    Mar 2013
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to calculate "first of the month after 60 days"

    Got it, the 03/02 start date would be 06/01.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com