number of days in a given month (1 Viewer)

hooi

Registered User.
Local time
Today, 21:31
Joined
Jul 22, 2003
Messages
158
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

Back once again...
Local time
Today, 13:31
Joined
Dec 10, 2002
Messages
11,316
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

Registered User.
Local time
Today, 21:31
Joined
Jul 22, 2003
Messages
158
Thank you...
 

SilentBreaker

Registered User.
Local time
Today, 13:31
Joined
Aug 7, 2003
Messages
28
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

Back once again...
Local time
Today, 13:31
Joined
Dec 10, 2002
Messages
11,316
I've already demonstrated to you the DateDiff function.

DateDiff( time interval, start date, end date)
 

hooi

Registered User.
Local time
Today, 21:31
Joined
Jul 22, 2003
Messages
158
I've now tested it and realise that it works just the way I wanted. Thank you Mile-O-Phile
 
Last edited:

Mile-O

Back once again...
Local time
Today, 13:31
Joined
Dec 10, 2002
Messages
11,316
=DateDiff("d",DateSerial(Year([txtStart]),Month([txtStart]),1),DateAdd("d",-1,DateAdd("m",1,DateSerial(Year([txtEnd]),Month([txtEnd]),1))))+1
 

hooi

Registered User.
Local time
Today, 21:31
Joined
Jul 22, 2003
Messages
158
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

AWF VIP
Local time
Today, 08:31
Joined
Jun 2, 2001
Messages
2,734
And, if you're not getting paid by the character and would like to keep it brief, you might try (from the debug window):
Code:
mydate = #2/13/03#
numdays = day(DateSerial(Year(MyDate), Month(MyDate) + 1, 0))
? numdays
 28

Best wishes,

Bob
 

ghudson

Registered User.
Local time
Today, 09:31
Joined
Jun 8, 2002
Messages
6,195
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! :D
 

hooi

Registered User.
Local time
Today, 21:31
Joined
Jul 22, 2003
Messages
158
Great! nice to see solutions from different angles. Thanks...
 

ashleedawg

"Here for a good time"
Local time
Today, 06:31
Joined
Jun 22, 2017
Messages
154
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.)




Code:
  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_

Longboard on the internet
Local time
Today, 06:31
Joined
Sep 12, 2017
Messages
2,111
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

Registered User.
Local time
Tomorrow, 00:31
Joined
Jul 4, 2013
Messages
2,770
@Ashlee, you are responding to a thread started over 15 years ago.
 

louries

New member
Local time
Today, 18:31
Joined
Feb 21, 2023
Messages
1
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.)




Code:
  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

Banishment Pending
Local time
Today, 08:31
Joined
May 11, 2011
Messages
11,613
Uh oh the resurrections are getting shorter. See you all in late 2024
 

Users who are viewing this thread

Top Bottom