Solved

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

Posted on 2003-10-22
7
405 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 50

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

808 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