Finding the last day of a month

[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!

 

[Allen Browne]

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.

 

[MikeB]

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.

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.

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

 

[Douglas J. Steele]

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

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

 

[Stuart McCall]

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

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

Plus the IsDate function handles this already:

Debug.Print Isdate("29 February 2007")
Result: False
Debug.Print Isdate("29 February 2008")
Result: True

 

[Klatuu]

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

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

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

Plus the IsDate function handles this already:

Debug.Print Isdate("29 February 2007")
Result: False
Debug.Print Isdate("29 February 2008")
Result: True

 

[MikeB]

Plus the IsDate function handles this already:

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.

 

[MikeB]

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

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

I understood this to be a separate Q.

no worries.