Calculating the number of days in a month

[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

 

[Victor Delgadillo]

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]

Thank you very much!

 

[Victor Delgadillo]

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.

_

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

 

[Douglas J. Steele]

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

 

[Victor Delgadillo]

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!

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

 

[Douglas J. Steele]

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)

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!

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

 

[Victor Delgadillo]

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!

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)

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!

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

 

[Douglas J. Steele]

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)

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!

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.

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