Solved

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

Posted on 2003-10-22
7
372 Views
Last Modified: 2008-02-01
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?

Thanks in advance!
0
Comment
Question by:Piet_Viseelt
7 Comments
 
LVL 16

Expert Comment

by:ahmedbahgat
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

by:Piet_Viseelt
ID: 9597054
Hi Ahmedbahgat.

The format is DD-MM-YYYY

0
 
LVL 1

Accepted Solution

by:
Geerd earned 55 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 18

Assisted Solution

by:bonjour-aut
bonjour-aut earned 20 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

by:ahmedbahgat
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 49

Expert Comment

by:Ryan Chong
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

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

FirstDay = inDate-Day(inDate)+1
LastDay = DateAdd("m",1,inDate)-Day(inDate)

Of course, there are many ways to do it.

Joe
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now