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

1. ## 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.

2. ## 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. ## 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

4. ## 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. ## 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. ## 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. ## 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>

#### Posting Permissions

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