Finding the last day of a month

  • Thread starter Thread starter Alejandro
  • Start date Start date
A

Alejandro

Hello there,

I would like to figure out the last day of a month, given a specific month
and year. Not only would I like to know whether the last day is 28, 30, or 31
(based on the month...this is probably the easy part) but I would also like
to know, given a specific year, if February had 28 or 29 days.

Any ideas?

Thanks!
 
The last day of the month is:
DateSerial(Year([MyDate]), Month([MyDate]) + 1, 0)
Replace MyDate with the name of your date field.

Access handles that correctly, even for December.

If you just want the day number, add Day() around the expression.
 
Allen Browne said:
The last day of the month is:
DateSerial(Year([MyDate]), Month([MyDate]) + 1, 0)
Replace MyDate with the name of your date field.

Access handles that correctly, even for December.

If you just want the day number, add Day() around the expression.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Alejandro said:
Hello there,

I would like to figure out the last day of a month, given a specific month
and year. Not only would I like to know whether the last day is 28, 30, or
31
(based on the month...this is probably the easy part) but I would also like
to know, given a specific year, if February had 28 or 29 days.
Click to expand...
Click to expand...

If it is a leap year, Feb has 29 days.

Public Function IsLeapYear(Yr As Integer) As Boolean
'Set default return value
IsLeapYear = False
'If year is divisible with four then it's a leap year, unless
'is divisible by 100 but not by 400...
If Yr Mod 4 = 0 Then
IsLeapYear = True
If Yr Mod 100 = 0 Then
If (Yr Mod 400) Then IsLeapYear = False
End If
End If
End Function
 
MikeB said:
Alejandro said:
Hello there,

I would like to figure out the last day of a month, given a specific
month
and year. Not only would I like to know whether the last day is 28, 30,
or 31
(based on the month...this is probably the easy part) but I would also
like
to know, given a specific year, if February had 28 or 29 days.
Click to expand...

The last day of the month is:
DateSerial(Year([MyDate]), Month([MyDate]) + 1, 0)
Replace MyDate with the name of your date field.

Access handles that correctly, even for December.

If you just want the day number, add Day() around the expression.
Click to expand...

If it is a leap year, Feb has 29 days.

Public Function IsLeapYear(Yr As Integer) As Boolean
'Set default return value
IsLeapYear = False
'If year is divisible with four then it's a leap year, unless
'is divisible by 100 but not by 400...
If Yr Mod 4 = 0 Then
IsLeapYear = True
If Yr Mod 100 = 0 Then
If (Yr Mod 400) Then IsLeapYear = False
End If
End If
End Function
Click to expand...

The formula Allen gave takes care of leap years as well. There's really no
need for this function.
 
Douglas J. Steele said:
MikeB said:
Hello there,

I would like to figure out the last day of a month, given a specific
month
and year. Not only would I like to know whether the last day is 28, 30,
or 31
(based on the month...this is probably the easy part) but I would also
like
to know, given a specific year, if February had 28 or 29 days.

The last day of the month is:
DateSerial(Year([MyDate]), Month([MyDate]) + 1, 0)
Replace MyDate with the name of your date field.

Access handles that correctly, even for December.

If you just want the day number, add Day() around the expression.
Click to expand...

If it is a leap year, Feb has 29 days.

Public Function IsLeapYear(Yr As Integer) As Boolean
'Set default return value
IsLeapYear = False
'If year is divisible with four then it's a leap year, unless
'is divisible by 100 but not by 400...
If Yr Mod 4 = 0 Then
IsLeapYear = True
If Yr Mod 100 = 0 Then
If (Yr Mod 400) Then IsLeapYear = False
End If
End If
End Function
Click to expand...

The formula Allen gave takes care of leap years as well. There's really no
need for this function.
Click to expand...

Plus the IsDate function handles this already:

Debug.Print Isdate("29 February 2007")
Result: False
Debug.Print Isdate("29 February 2008")
Result: True
 
Interesting how many ways you can get to the same place:
day(dateserial(2007,3,0)) returns 28
day(dateserial(2008,3,0)) returns 29
--
Dave Hargis, Microsoft Access MVP


Stuart McCall said:
Douglas J. Steele said:
MikeB said:
Hello there,

I would like to figure out the last day of a month, given a specific
month
and year. Not only would I like to know whether the last day is 28, 30,
or 31
(based on the month...this is probably the easy part) but I would also
like
to know, given a specific year, if February had 28 or 29 days.

The last day of the month is:
DateSerial(Year([MyDate]), Month([MyDate]) + 1, 0)
Replace MyDate with the name of your date field.

Access handles that correctly, even for December.

If you just want the day number, add Day() around the expression.


If it is a leap year, Feb has 29 days.

Public Function IsLeapYear(Yr As Integer) As Boolean
'Set default return value
IsLeapYear = False
'If year is divisible with four then it's a leap year, unless
'is divisible by 100 but not by 400...
If Yr Mod 4 = 0 Then
IsLeapYear = True
If Yr Mod 100 = 0 Then
If (Yr Mod 400) Then IsLeapYear = False
End If
End If
End Function
Click to expand...

The formula Allen gave takes care of leap years as well. There's really no
need for this function.
Click to expand...

Plus the IsDate function handles this already:

Debug.Print Isdate("29 February 2007")
Result: False
Debug.Print Isdate("29 February 2008")
Result: True
Click to expand...
 
Stuart McCall said:
Plus the IsDate function handles this already:
Click to expand...

Yep. I just didn't think in terms of throwing a bogus date construct at it
to get the boolean response, and besides I spent a little time constructing
this function in another language maybe 12 years ago, and still use it today.
Not saying I won't use IsDate, just that I am habituated to this because it
resides in my standard modMain.bas (I know you have one of your own (-; ).

cheers.
 
Douglas J. Steele said:
MikeB said:
Hello there,

I would like to figure out the last day of a month, given a specific month
and year. Not only would I like to know whether the last day is 28, 30, or
31
(based on the month...this is probably the easy part) but I would also
like
to know, given a specific year, if February had 28 or 29 days.

The last day of the month is:
DateSerial(Year([MyDate]), Month([MyDate]) + 1, 0)
Replace MyDate with the name of your date field.

Access handles that correctly, even for December.

If you just want the day number, add Day() around the expression.
Click to expand...

If it is a leap year, Feb has 29 days.

Public Function IsLeapYear(Yr As Integer) As Boolean
'Set default return value
IsLeapYear = False
'If year is divisible with four then it's a leap year, unless
'is divisible by 100 but not by 400...
If Yr Mod 4 = 0 Then
IsLeapYear = True
If Yr Mod 100 = 0 Then
If (Yr Mod 400) Then IsLeapYear = False
End If
End If
End Function
Click to expand...

The formula Allen gave takes care of leap years as well. There's really no
need for this function.
Click to expand...

I understood this to be a separate Q.

no worries.
 
Back
Top