Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# VB or VBA function for determining the last day of a month

Posted on 2003-10-22
Medium Priority
430 Views
Hi Everybody!

I need to make a VB of VBA function that determines the last day of a month. The routine should always work. Anybody got a idea?

0
Question by:Piet_Viseelt
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 16

Expert Comment

ID: 9597029
what will be the arguments to pass to the function will it be , month and year?? and in what format 2/2003 or jan 2003

cheers
0

Author Comment

ID: 9597054
Hi Ahmedbahgat.

The format is DD-MM-YYYY

0

LVL 1

Accepted Solution

Geerd earned 220 total points
ID: 9597076
Heej Piet,

Take a look at the function below.

Function IsUltimo(Indate As String) As Integer

Dim leap As Integer
If Val(Right\$(Indate, 4)) Mod 4 = 0 Then leap = 1

Select Case Mid\$(Indatum, 4, 2)
Case "01": IsUltimo = 31
Case "02": IsUltimo = 28 + leap
Case "03": IsUltimo = 31
Case "04": IsUltimo = 30
Case "05": IsUltimo = 31
Case "06": IsUltimo = 30
Case "07": IsUltimo = 31
Case "08": IsUltimo = 31
Case "09": IsUltimo = 30
Case "10": IsUltimo = 31
Case "11": IsUltimo = 30
Case "12": IsUltimo = 31
End Select

End Function

Suc 6
0

LVL 18

Assisted Solution

bonjour-aut earned 80 total points
ID: 9597117
to get the last day of month for a given inDate:

lastDay = DateSerial(Year(inDate),Month(inDate)+1,1)-1

exeption is December:

lastDay = DateSerial(Year(inDate)+1,Month(inDate)+1,1)-1

Regards, Franz

0

LVL 16

Expert Comment

ID: 9597118
what do you need to know then based on that date? is it  sunday, monday, etc.... as apposed to the last day of the month of that date argument

cheers
0

LVL 53

Expert Comment

ID: 9597183
>>exeption is December:

>>lastDay = DateSerial(Year(inDate)+1,Month(inDate)+1,1)-1

I think use the :
lastDay = DateSerial(Year(inDate),Month(inDate)+1,1)-1
should work for all instance.

:-)
0

LVL 11

Expert Comment

ID: 9598409
We will stick with Franz' naming convention here with inDate as the Date you will provide.

FirstDay = inDate-Day(inDate)+1

Of course, there are many ways to do it.

Joe
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
###### Suggested Courses
Course of the Month11 days, 17 hours left to enroll