Results 1 to 9 of 9

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

This is a discussion on How to calculate "first of the month after 60 days" within the Excel Questions forums, part of the Question Forums category; Hello, I am having trouble creating a formula to capture the "first of the month after 60 days" from the ...

  1. #1
    New Member
    Join Date
    Mar 2013
    Posts
    7

    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
    32,911

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

    Try

    =(A1+60)-DAY(A1+60)+1
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    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,443

    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
    32,911

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

    Strike that last, misunderstood slightly..

    par's got the right answer.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    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
    19,957

    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
    7

    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
    7

    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,443

    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
    7

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

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

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
  •  


DMCA.com