6 Replies - 3552 Views - Last Post: 22 November 2019 - 04:03 AM

#1 firebolt   User is offline

  • D.I.C Lover
  • member icon

Reputation: 93
  • View blog
  • Posts: 5,561
  • Joined: 20-February 09

Last Day

Posted 12 May 2009 - 05:28 AM

Description: Finds the last day of any given month.
Function Last(Month As Byte, Year As Integer) As Date
    
    Dim Day As Byte
    
    For Day = 1 To 32        
        If Not IsDate(MonthNbr & "/" & Day & "/" & Year) Then            
            Day = Day - 1
            Last = Date(Month & "/" & Day & "/" & Year)
            Exit Function        
        End If    
    Next Day
    
End Function

Is This A Good Question/Topic? 0
  • +

Replies To: Last Day

#2 firebolt   User is offline

  • D.I.C Lover
  • member icon

Reputation: 93
  • View blog
  • Posts: 5,561
  • Joined: 20-February 09

Re: Last Day

Posted 12 May 2009 - 05:28 AM

Description: Finds the last day of any given month.
Function Last(Month As Byte, Year As Integer) As Date
    
    Dim Day As Byte
    
    For Day = 1 To 32        
        If Not IsDate(Month & "/" & Day & "/" & Year) Then            
            Day = Day - 1
            Last = Date(Month & "/" & Day & "/" & Year)
            Exit Function        
        End If    
    Next Day
    
End Function

Was This Post Helpful? 0
  • +
  • -

#3 HitchCraft   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 4
  • Joined: 27-January 15

Re: Last Day

Posted 27 January 2015 - 10:02 AM

A different method to accomplish the same result:
Private Function FindLastDay(ByVal iMonth As Integer, ByVal iYear As Integer) As Integer
    Dim FirstDay As Date
    Dim NextMonth As Date
    Dim LastDay As Date
    FirstDay = CDate(Format(iMonth, "00") & "/01/" & Format(iYear, "0000"))
    NextMonth = DateAdd("m", 1, FirstDay)
    LastDay = DateAdd("d", -1, NextMonth)
    FindLastDay = Int(Val(Format(LastDay, "D")))
End Function


Was This Post Helpful? 0
  • +
  • -

#4 Atilla999   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 20-November 19

Re: Last Day

Posted 21 November 2019 - 09:26 AM

or yet another shorter approach
Function lastDay(dd As Date) As Integer
    lastDay = Day(WorksheetFunction.EoMonth(dd, 0))
End Function


Was This Post Helpful? 0
  • +
  • -

#5 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15488
  • View blog
  • Posts: 62,031
  • Joined: 12-June 08

Re: Last Day

Posted 21 November 2019 - 09:31 AM

That would require a whole different includes.. interop for excel and all that jazz.
Was This Post Helpful? 1
  • +
  • -

#6 Atilla999   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 20-November 19

Re: Last Day

Posted 21 November 2019 - 01:30 PM

Minor adjustment: Start the for loop at 28.
This saves 27 useless iterations (those dates are always valid.
Was This Post Helpful? 0
  • +
  • -

#7 Atilla999   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 20-November 19

Re: Last Day

Posted 22 November 2019 - 04:03 AM

I believe this is a quick way to find the last day with only a few lines of code.

Function Last(Month As Byte, Year As Integer) As Date
    
    Dim Day As Byte
    
    Day = 32
    Do
        Day = Day - 1
    Loop Until IsDate(Month & "/" & Day & "/" & Year)
    Last = CDate(Month & "/" & Day & "/" & Year)
                
End Function


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1