Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Loungers - I'm trying to work out a formula that will calculate the last day of a previous month. ie in cell B1 if have the last day of a month - say 28/2/09 in A1 I need the last day of January - 31/1/09, or 30/6/09 in A1 - B1 needs to be 31/5/09

    I have tried subtracting one day and one month from the date in A1, however that gives dates that don't exist or not the last day of the previous month.

    I hope this makes sense - any thoughts?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [quote name='verada' post='782811' date='03-Jul-2009 12:28']Loungers - I'm trying to work out a formula that will calculate the last day of a previous month. ie in cell B1 if have the last day of a month - say 28/2/09 in A1 I need the last day of January - 31/1/09, or 30/6/09 in A1 - B1 needs to be 31/5/09

    I have tried subtracting one day and one month from the date in A1, however that gives dates that don't exist or not the last day of the previous month.

    I hope this makes sense - any thoughts?[/quote]

    The last day of the previous month is one day before the first day of the month you have so:

    =DATE(YEAR(B1),MONTH(B1),1)-1
    Regards
    John



  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Cheers - just what I needed

    Thanks

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='johnhutchison' post='782818' date='02-Jul-2009 21:04']The last day of the previous month is one day before the first day of the month you have so:

    =DATE(YEAR(B1),MONTH(B1),1)-1[/quote]
    Hi John
    Your formula can be simplified slightly to:
    =DATE(YEAR(B1),MONTH(B1),0)
    Regards
    Don

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [quote name='wdwells' post='782824' date='03-Jul-2009 14:53']Hi John
    Your formula can be simplified slightly to:
    =DATE(YEAR(B1),MONTH(B1),0)[/quote]

    Thanks Don

    In theory, I "know" that, but in practice I find the "day before the first day of the month" concept easier to remember when I need to do this.
    Regards
    John



  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Yet another option: if the date is in A1, the formula

    =A1-DAY(A1)

    will return the last day of the previous month.

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='782844' date='03-Jul-2009 05:54']Yet another option: if the date is in A1, the formula

    =A1-DAY(A1)

    will return the last day of the previous month.[/quote]

    Thanks Hans

    I've seen you use that before but had not remembered it. I like it for its brevity.
    Regards
    Don

Posting Permissions

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