number of days in a given month

[hooi]

Iv'e got two questions:

When a user supplies a date (say 2/13/2003).
1. I'd want to know how to find out what month it is; and
2. how many days are there for the given month

Thanks for helping...

 

[Mile-O]

1. Use the Month() function.

2. DateDiff("d", DateSerial(Year([YourDate]), Month([Your Date]), 1), DateAdd("d", -1, DateSerial(Year(DateAdd("d", 1, [Your Date])), Month(DateAdd("d", 1, [Your Date])), 1)))

 

[hooi]

Thank you...

 

[SilentBreaker]

1. Use the function of DatePart to return the value of month


DatePart("m", #date#)


2. I have created a function for you, to return how many days for a given month.

Function HowManyDays(ByVal strDate As String) As Integer
Dim i As Integer
On Error Resume Next

Err.Clear
i = 1 'intialise
Do While Day(Format(CStr(i) & Format(strDate, "/mm/yyyy"), "dd/mm/yyyy")) > 0
If Err > 0 Then Exit Do
i = i + 1
Loop

HowManyDays = i - 1

End Function

open the debug window and type like this:

?howmanydays(#02/04/88#)
29

Hope this help you, cheers

 

[Mile-O]

I've already demonstrated to you the DateDiff function.

DateDiff( time interval, start date, end date)

 

[hooi]

I've now tested it and realise that it works just the way I wanted. Thank you Mile-O-Phile

 

[Mile-O]

=DateDiff("d",DateSerial(Year([txtStart]),Month([txtStart]),1),DateAdd("d",-1,DateAdd("m",1,DateSerial(Year([txtEnd]),Month([txtEnd]),1))))+1

 

[hooi]

Looks like I'm begining to enjoy the Edit feature to modify my posting. (I shall refrain from doing that to avoid confusion).

Thanks again Mile-o-Phile.

 

[raskew]

And, if you're not getting paid by the character and would like to keep it brief, you might try (from the debug window):

mydate = #2/13/03#
numdays = day(DateSerial(Year(MyDate), Month(MyDate) + 1, 0))
? numdays
 28

Best wishes,

Bob

 

[ghudson]

Thanks raskew, you just solved a problem [wish list] that I have had on the backburner.

Sub Test()
NumberOfDaysInMonth ("2/01/2000")
End Sub

Public Function NumberOfDaysInMonth(dDate As Date)
Dim NumberOfDays As Integer
NumberOfDays = Day(DateSerial(Year(dDate), Month(dDate) + 1, 0))
MsgBox NumberOfDays
End Function

Correctly returns 29. February 2000 was a leap year!

Thanks!

 

[hooi]

Great! nice to see solutions from different angles. Thanks...

 

[ashleedawg]

When using date functions provided by others, it's important to diligently test the functions across several sets of data (or dates, in this case). Just because a function seems to works properly/accurately/error-free on today's date, that doesn't mean it will be accurate for the whole year.


For example, one of the other solutions starts by adding "1" to the month number, the get the date of the 1st of the next month. That's fantastic... unless it's December. (unless there's a new "month #13" I'm unaware of!)


When a project uses a lot of date or math functions that aren't available in Access but are in Excel, I prefer to call Excel's built-in functions from Access VBA instead of trying to recreate them.


The function below uses the WorksheetFunction object in Excel's library to return the number of days in the month of the date specified. (Since it's late-bound, no references are required.)

  Option Explicit
  
  Public xl As Object
  
  Function daysInMonth(dt As Date) As Integer
  'Returns the number of dates in the month of [dt]
    'create Excel object - but only if one doesn't already exist
    If xl Is Nothing Then Set xl = CreateObject("Excel.Application")
    'get the last day of the month for the specified date
    daysInMonth = Day(xl.WorksheetFunction.EOMonth(dt, 0))
  End Function
  
  Sub demo()
    MsgBox "There are " & daysInMonth(Now()) & " days in this month."
  End Sub

 

[Mark_]

Two items of note regarding this thread...
First, it is getting close to heading off to university.
Second, DateSerial is smart enough to return the proper date when you pass it a month greater than 12. It does go to the next year without issue. It is rather bright about these things and can even handle being passed a negative value. It doesn't like Null, but that is rather to be expected.

 

[Cronk]

@Ashlee, you are responding to a thread started over 15 years ago.

 

[louries]

When using date functions provided by others, it's important to diligently test the functions across several sets of data (or dates, in this case). Just because a function seems to works properly/accurately/error-free on today's date, that doesn't mean it will be accurate for the whole year.


For example, one of the other solutions starts by adding "1" to the month number, the get the date of the 1st of the next month. That's fantastic... unless it's December. (unless there's a new "month #13" I'm unaware of!)


When a project uses a lot of date or math functions that aren't available in Access but are in Excel, I prefer to call Excel's built-in functions from Access VBA instead of trying to recreate them.


The function below uses the WorksheetFunction object in Excel's library to return the number of days in the month of the date specified. (Since it's late-bound, no references are required.)

  Option Explicit

  Public xl As Object

  Function daysInMonth(dt As Date) As Integer
  'Returns the number of dates in the month of [dt]
    'create Excel object - but only if one doesn't already exist
    If xl Is Nothing Then Set xl = CreateObject("Excel.Application")
    'get the last day of the month for the specified date
    daysInMonth = Day(xl.WorksheetFunction.EOMonth(dt, 0))
  End Function

  Sub demo()
    MsgBox "There are " & daysInMonth(Now()) & " days in this month."
  End Sub

This assumes creation_date is no later than approved_date. Creation_date is not included in the results, but approved_date is included.

 

[plog]

Uh oh the resurrections are getting shorter. See you all in late 2024

 

[nolanjack]

I have test it.