Results 1 to 7 of 7
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Calculate Last Date of Prev Month (2000/SR-1)

    I need to fill a text box with the last date of the previous month for financial calculations. Is there an easy way to figure this?

    I'm sure I have done it in the past and am hitting a blank wall.
    Alan

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

    Re: Calculate Last Date of Prev Month (2000/SR-1)

    Try this formula:

    =DATE(YEAR(TODAY()),MONTH(TODAY()),0)

    The "0-th day" of the current month is the last day of the previous month.

  3. #3
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Calculate Last Date of Prev Month (2000/SR-1)

    I remembered my calcaulation:
    <pre> 'Display form for Date entry
    x = Month(Now())
    y = x - 1
    If y = 0 Then y = 12 <font color=red>'Month</font color=red>
    x = Year(Now())
    If y = 12 Then arb = x - 1 Else arb = x <font color=red>'Year</font color=red>
    Select Case y
    Case 1, 3, 5, 7, 8, 10, 12
    daDate = y & "/" & 31 & "/" & arb
    Case 4, 6, 9, 11
    datate = y & "/" & 30 & "/" & arb
    Case 2
    If x Mod 4 = 0 Then <font color=red>'Don't need to worry about 500 year rule for a long time</font color=red>
    daDate = y & "/" & 29 & "/" & arb
    Else
    daDate = y & "/" & 28 & "/" & arb
    End If
    End Select
    frmDate.txtDate.Text = FormatDateTime(daDate, vbShortDate)</pre>

    Alan

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

    Re: Calculate Last Date of Prev Month (2000/SR-1)

    Or the equivalent of the formula I posted in VBA:

    frmDate.txtDate.Text = DateSerial(Year(Date), Month(Date), 0)

  5. #5
    Lounger
    Join Date
    Jan 2004
    Location
    Derry, Derry, Ireland, Northern
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculate Last Date of Prev Month (2000/SR-1)

    Hi Macro Alan,

    Use Month() and Year () to get those values from your initial date. Then use Date(x,y,1) where x is the year value and y is the month value of your initial date. This will give the first day of the month which your initial date resides in. Then subtract 1 from that date = last date of the previous month.

    HTH

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculate Last Date of Prev Month (2000/SR-1)

    If you have a series of month end dates, then use = Date[year(previous value),month(previous value)+2,day(1)]-1

  7. #7
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Calculate Last Date of Prev Month (2000/SR-1)

    Works fantastic! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>
    Alan

Posting Permissions

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