Calculating the number of days in a month

  • Thread starter Thread starter AJF
  • Start date Start date
A

AJF

Hello,

Is there a way to automatically calculate the number of
days in any given month. We are attempting to create
queries that will calculate what percentage of a month is
complete. (example: day(now())/31 ). Any suggestions?

Thanks,

AJF
 
To find out how many days in a given month:

intDaysInMonth = day(cdate(trim(Str(Month(MyDate)+1))& "/01/" &
trim(str(Year(MyDate))))-1)

This will give you the number of days in the month of the date in the
variable MyDate. You may replace in the above formula the MyDate for Now(),
watch out for the parentheses...

dblPercent = day(now()/intDaysInMonth) will answer your question.

--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_
 
Correction on the last formula:
dblPercent = day(now())/intDaysInMonth will answer your question.

The function day(now()) should be calculated before dividing by the number
of days in the month...
Sorry!!



--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_
Victor Delgadillo said:
To find out how many days in a given month:

intDaysInMonth = day(cdate(trim(Str(Month(MyDate)+1))& "/01/" &
trim(str(Year(MyDate))))-1)

This will give you the number of days in the month of the date in the
variable MyDate. You may replace in the above formula the MyDate for Now(),
watch out for the parentheses...

dblPercent = day(now()/intDaysInMonth) will answer your question.

--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_
AJF said:
Hello,

Is there a way to automatically calculate the number of
days in any given month. We are attempting to create
queries that will calculate what percentage of a month is
complete. (example: day(now())/31 ). Any suggestions?

Thanks,

AJF
Click to expand...
Click to expand...
 
Victor: That won't work if the user has his/her Short Date format set to
dd/mm/yyyy

To ensure that it'll work for everyone, use the DateSerial function:

intDaysInMonth = Day(DateSerial(Year(MyDate), Month(MyDate) + 1, 0))


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Victor Delgadillo said:
To find out how many days in a given month:

intDaysInMonth = day(cdate(trim(Str(Month(MyDate)+1))& "/01/" &
trim(str(Year(MyDate))))-1)

This will give you the number of days in the month of the date in the
variable MyDate. You may replace in the above formula the MyDate for Now(),
watch out for the parentheses...

dblPercent = day(now()/intDaysInMonth) will answer your question.

--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_
AJF said:
Hello,

Is there a way to automatically calculate the number of
days in any given month. We are attempting to create
queries that will calculate what percentage of a month is
complete. (example: day(now())/31 ). Any suggestions?

Thanks,

AJF
Click to expand...
Click to expand...
 
Doug, I'm assuming the "field" is a time/date type, not a formatted string.
A time/date field will contain a doble precision number with the date and
time stored on it, and all the date/time functions will work on it.


--
Victor Delgadillo MS-MVP Access
Miami, Florida

Mensajes a los grupos de noticia, asi todos nos beneficiamos!



Douglas J. Steele said:
Victor: That won't work if the user has his/her Short Date format set to
dd/mm/yyyy

To ensure that it'll work for everyone, use the DateSerial function:

intDaysInMonth = Day(DateSerial(Year(MyDate), Month(MyDate) + 1, 0))


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Victor Delgadillo said:
To find out how many days in a given month:

intDaysInMonth = day(cdate(trim(Str(Month(MyDate)+1))& "/01/" &
trim(str(Year(MyDate))))-1)

This will give you the number of days in the month of the date in the
variable MyDate. You may replace in the above formula the MyDate for Now(),
watch out for the parentheses...

dblPercent = day(now()/intDaysInMonth) will answer your question.

--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_
AJF said:
Hello,

Is there a way to automatically calculate the number of
days in any given month. We are attempting to create
queries that will calculate what percentage of a month is
complete. (example: day(now())/31 ). Any suggestions?

Thanks,

AJF
Click to expand...
Click to expand...
Click to expand...
 
I hear you, but I still think you're incorrect.

You're using CDate, which respects the user's Short Date format.

intDaysInMonth = day(cdate(trim(Str(Month(MyDate)+1))& "/01/" &
trim(str(Year(MyDate))))-1)

That means that, for instance, this month you're going to be looking at
cdate("11/01/2003"). If the user has a Short Date format of dd/mm/yyyy, that
will translate to 11 Jan, 2003, not 01 Nov, 2003. That means that when you
subtract one from it, you'll get 10 Jan, 2003, so that intDaysInMonth will
be 10.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Victor Delgadillo said:
Doug, I'm assuming the "field" is a time/date type, not a formatted string.
A time/date field will contain a doble precision number with the date and
time stored on it, and all the date/time functions will work on it.


--
Victor Delgadillo MS-MVP Access
Miami, Florida

Mensajes a los grupos de noticia, asi todos nos beneficiamos!



Douglas J. Steele said:
Victor: That won't work if the user has his/her Short Date format set to
dd/mm/yyyy

To ensure that it'll work for everyone, use the DateSerial function:

intDaysInMonth = Day(DateSerial(Year(MyDate), Month(MyDate) + 1, 0))


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Victor Delgadillo said:
To find out how many days in a given month:

intDaysInMonth = day(cdate(trim(Str(Month(MyDate)+1))& "/01/" &
trim(str(Year(MyDate))))-1)

This will give you the number of days in the month of the date in the
variable MyDate. You may replace in the above formula the MyDate for Now(),
watch out for the parentheses...

dblPercent = day(now()/intDaysInMonth) will answer your question.

--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_
Hello,

Is there a way to automatically calculate the number of
days in any given month. We are attempting to create
queries that will calculate what percentage of a month is
complete. (example: day(now())/31 ). Any suggestions?

Thanks,

AJF
Click to expand...
Click to expand...
Click to expand...
 
Yes, of course, if the user is using the European date system too, he should
adjust for the formatting. The idea is to create a date that will be the
first day of the following month and then subtract one day. That will give
the days on that month. Users should adjust the formula if they are using
other format than the standard US Date mm/dd/yyyy.

--
Victor Delgadillo MS-MVP Access
Miami, Florida

Mensajes a los grupos de noticia, asi todos nos beneficiamos!



Douglas J. Steele said:
I hear you, but I still think you're incorrect.

You're using CDate, which respects the user's Short Date format.

intDaysInMonth = day(cdate(trim(Str(Month(MyDate)+1))& "/01/" &
trim(str(Year(MyDate))))-1)

That means that, for instance, this month you're going to be looking at
cdate("11/01/2003"). If the user has a Short Date format of dd/mm/yyyy, that
will translate to 11 Jan, 2003, not 01 Nov, 2003. That means that when you
subtract one from it, you'll get 10 Jan, 2003, so that intDaysInMonth will
be 10.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Victor Delgadillo said:
Doug, I'm assuming the "field" is a time/date type, not a formatted string.
A time/date field will contain a doble precision number with the date and
time stored on it, and all the date/time functions will work on it.


--
Victor Delgadillo MS-MVP Access
Miami, Florida

Mensajes a los grupos de noticia, asi todos nos beneficiamos!



Douglas J. Steele said:
Victor: That won't work if the user has his/her Short Date format set to
dd/mm/yyyy

To ensure that it'll work for everyone, use the DateSerial function:

intDaysInMonth = Day(DateSerial(Year(MyDate), Month(MyDate) + 1, 0))


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



To find out how many days in a given month:

intDaysInMonth = day(cdate(trim(Str(Month(MyDate)+1))& "/01/" &
trim(str(Year(MyDate))))-1)

This will give you the number of days in the month of the date in the
variable MyDate. You may replace in the above formula the MyDate for
Now(),
watch out for the parentheses...

dblPercent = day(now()/intDaysInMonth) will answer your question.

--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_
Hello,

Is there a way to automatically calculate the number of
days in any given month. We are attempting to create
queries that will calculate what percentage of a month is
complete. (example: day(now())/31 ). Any suggestions?

Thanks,

AJF
Click to expand...
Click to expand...
Click to expand...
 
As I posted before, using DateSerial means you have one solution will work
anywhere:

intDaysInMonth = Day(DateSerial(Year(MyDate), Month(MyDate) + 1, 0))

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Victor Delgadillo said:
Yes, of course, if the user is using the European date system too, he should
adjust for the formatting. The idea is to create a date that will be the
first day of the following month and then subtract one day. That will give
the days on that month. Users should adjust the formula if they are using
other format than the standard US Date mm/dd/yyyy.

--
Victor Delgadillo MS-MVP Access
Miami, Florida

Mensajes a los grupos de noticia, asi todos nos beneficiamos!



Douglas J. Steele said:
I hear you, but I still think you're incorrect.

You're using CDate, which respects the user's Short Date format.

intDaysInMonth = day(cdate(trim(Str(Month(MyDate)+1))& "/01/" &
trim(str(Year(MyDate))))-1)

That means that, for instance, this month you're going to be looking at
cdate("11/01/2003"). If the user has a Short Date format of dd/mm/yyyy, that
will translate to 11 Jan, 2003, not 01 Nov, 2003. That means that when you
subtract one from it, you'll get 10 Jan, 2003, so that intDaysInMonth will
be 10.
Click to expand...
set
to
dd/mm/yyyy

To ensure that it'll work for everyone, use the DateSerial function:

intDaysInMonth = Day(DateSerial(Year(MyDate), Month(MyDate) + 1, 0))


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



To find out how many days in a given month:

intDaysInMonth = day(cdate(trim(Str(Month(MyDate)+1))& "/01/" &
trim(str(Year(MyDate))))-1)

This will give you the number of days in the month of the date in the
variable MyDate. You may replace in the above formula the MyDate for
Now(),
watch out for the parentheses...

dblPercent = day(now()/intDaysInMonth) will answer your question.

--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_
Hello,

Is there a way to automatically calculate the number of
days in any given month. We are attempting to create
queries that will calculate what percentage of a month is
complete. (example: day(now())/31 ). Any suggestions?

Thanks,

AJF
Click to expand...
Click to expand...
Click to expand...
 
Back
Top