Solved

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

Posted on 2003-10-22
7
385 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

910 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

19 Experts available now in Live!

Get 1:1 Help Now