Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Begining of month formula (Excel 2003)

    HI

    I have used the EOMONTH formula is there one for Begining of the month?

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Begining of month formula (Excel 2003)

    I miss your point. Isn't the beginning of a month always the first:

    =DATE(2005,5,1)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Begining of month formula (Excel 2003)

    If you have a date in cell A1, either of the following formulas will return the first day of the month of A1:

    =EOMONTH(A1,-1)+1

    =DATE(YEAR(A1),MONTH(A1),1)

    If you use the first one, you may have to set the format to a date format manually.

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Begining of month formula (Excel 2003)

    Hi Hans

    Sorry I was not clear in the first place.

    Start Date is 01/05/2005 so the review date would be 12 months hence, however I don't want the review date to be exactly to the day I want it to be the last day of the previous month, so it would be 30th April 2006

    This is my first formula =IF(Start_Date="","",EOMONTH(Start_Date,0)+365) which gives me a review date

    I have discovered since my post if I use =IF(Start_Date="","",EOMONTH(Start_Date,-1)+365) I get the result.

    I trust this is an explanation to Jan Karel's question too.

    Many Thanks
    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Begining of month formula (Excel 2003)

    That formula will not work correctly in leap years. Try 1 March 2003 for example. Your formula will result in 28 February 2004 instead of 29 February 2004. Instead, use

    =EOMONTH(Start_Date,11)

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Begining of month formula (Excel 2003)

    HI Hans

    Thanks very much. I must admit I did not consider leap years.


    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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