Closed Thread
Results 1 to 6 of 6

Date of the First day of the previous month

  1. #1
    Hari
    Guest

    Date of the First day of the previous month

    Hi,

    If am given a date like let say, 24-Jan-2006, how would I get the date
    1-Dec-2005?

    Presently I have used some seemingly complicated formula to get the
    first day of the previous month. I have used convoluted combination of
    DATE and EODATE function to achieve the same.

    I have 2 issues with this

    a) Probably there is an easy way to do this without using EODATE
    function

    b) This worksheet wont work for those who doesnt have Analysis Toolpak
    Add-in installed.

    Is there an alternate way for this?

    Please guide me.

    Regards,
    HP
    India


  2. #2
    Leo Heuser
    Guest

    Re: Date of the First day of the previous month

    "Hari" <[email protected]> skrev i en meddelelse
    news:[email protected]...
    > Hi,
    >
    > If am given a date like let say, 24-Jan-2006, how would I get the date
    > 1-Dec-2005?
    >
    > Presently I have used some seemingly complicated formula to get the
    > first day of the previous month. I have used convoluted combination of
    > DATE and EODATE function to achieve the same.
    >
    > I have 2 issues with this
    >
    > a) Probably there is an easy way to do this without using EODATE
    > function
    >
    > b) This worksheet wont work for those who doesnt have Analysis Toolpak
    > Add-in installed.
    >
    > Is there an alternate way for this?
    >
    > Please guide me.
    >
    > Regards,
    > HP
    > India
    >


    Hi HP

    One way, with known date in C2:

    =DATE(YEAR(C2),MONTH(C2)-1,1)


    --
    Best regards
    Leo Heuser

    Followup to newsgroup only please.



  3. #3
    Hari
    Guest

    Re: Date of the First day of the previous month

    Leo,

    Thanks a lot. I feel really stupid to not try this. I thought that
    Month(C2) -1 would give "error" in case value of Month is 1. So, I
    didnt even try it out.

    Regards,
    HP
    India


  4. #4
    Leo Heuser
    Guest

    Re: Date of the First day of the previous month

    "Hari" <[email protected]> skrev i en meddelelse
    news:[email protected]...
    > Leo,
    >
    > Thanks a lot. I feel really stupid to not try this. I thought that
    > Month(C2) -1 would give "error" in case value of Month is 1. So, I
    > didnt even try it out.
    >
    > Regards,
    > HP
    > India
    >

    You are welcome and thanks for the feedback.
    I know the feeling ;-)

    Regards
    Leo



  5. #5
    Registered User
    Join Date
    11-21-2011
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Date of the First day of the previous month

    How do you do this without referencing another cell?

    1st Day of Year 1/1/2011
    1st of Current Month 11/1/2011
    1st of Prev Month 10/1/2011
    1st of 2 months ago 9/1/2011
    1st of current week 11/20/2011
    1st of prev week 11/13/2011
    1st of 2 weeks ago 11/6/2011
    1st of 3 weeks ago 10/30/2011

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Date of the First day of the previous month

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1