Calculating Months Between Two Dates

[Guest]

Hi
I am having a major diffuclty with this: Look at this formula:
Month Calculation

=(((DateDiff("d",[receiveddate],IIf(IsNull([outdate]),Date(),
outdate])))<1),1,(DateDiff("m",[receiveddate],IIf(IsNull
([outdate]),Date(),[outdate]))))

This formula calcualtes the date difference between the 'received date' and
the 'ship date' if the 'ship date is blank' then the 'current date', then it
tells me how many months have gone by. If the month value is less than one
month, it automatically sets it to one. The problem I am having is this, I
need this formula to calculate a new month, as soon as 30 days have gone by.
For example, if the 'received date' is January 15, 2006 and the 'ship date'
was February 20, 2006, the way the formula is now, it will show me (1) month,
when in actuality should be (2), since the total of days is 37 days. That
means that when the 'current date' was February 14, 2006 it should
automatically changed to (2). Can you modify the formula above to do what I
have described here.

Since then, I have created a new formula:
Public Function MthsElapsed(dteRecDate As Date, dteOutDate As Date)
as Integer

If IsNull(dteOutDate) Then
dteOutDate = Date()
End If

MthsElapsed = DateDiff("m",dteRecDate,dteOutDate)
If Day(dteOutDate) > Day(dteRecDate) Then
MthsElapsed = MthsElapsed + 1
End If
End Function

This formula works half way. First, the line that reads 'dteOutDate =
Date()' everytime I tried typing the () and I hit enter to input the next
line of code, the parenthesis disappear. So the line ends up looking like
this:dteOutDate = Date

When I am finished with the code, I go into the actual form, after I have
changed the source to read =mthsElapsed([receiveddate],[outdate]), and I get
an ERROR in the area where the total months should dbe displayed. What I did
noticed was that if the Out Date has an actual date, it will go ahead and
calculate the months and display it correctly. But if the Out Date is Blank
it will give me an error, instead of doing the calculation based on the
actual date, since the customer calls and asks 'what are my current charges',
then the computer needs to be able to check the current date against the
received date and display the number of months to date, just like it is doing
with the out date.

Any help is greatly appreciated.

 

[Douglas J Steele]

To access, that IS 1 month, just as it's 1 year between 31 Dec, 2005 and 1
Jan, 2006.

Try using the same sort of calculation that's used to calculate age, where
you need to adjust the age depending on whether or not the birthday has
already occurred in the month:

DateDiff("m", [receiveddate], Nz([outdate], Date())) - _
IIf(Day(Nz([outdate], Date())) < Day([receiveddate]), 1, 0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi
I am having a major diffuclty with this: Look at this formula:
Month Calculation

=(((DateDiff("d",[receiveddate],IIf(IsNull([outdate]),Date(),
outdate])))<1),1,(DateDiff("m",[receiveddate],IIf(IsNull
([outdate]),Date(),[outdate]))))

This formula calcualtes the date difference between the 'received date' and
the 'ship date' if the 'ship date is blank' then the 'current date', then it
tells me how many months have gone by. If the month value is less than one
month, it automatically sets it to one. The problem I am having is this, I
need this formula to calculate a new month, as soon as 30 days have gone by.
For example, if the 'received date' is January 15, 2006 and the 'ship date'
was February 20, 2006, the way the formula is now, it will show me (1) month,
when in actuality should be (2), since the total of days is 37 days. That
means that when the 'current date' was February 14, 2006 it should
automatically changed to (2). Can you modify the formula above to do what I
have described here.

Since then, I have created a new formula:
Public Function MthsElapsed(dteRecDate As Date, dteOutDate As Date)
as Integer

If IsNull(dteOutDate) Then
dteOutDate = Date()
End If

MthsElapsed = DateDiff("m",dteRecDate,dteOutDate)
If Day(dteOutDate) > Day(dteRecDate) Then
MthsElapsed = MthsElapsed + 1
End If
End Function

This formula works half way. First, the line that reads 'dteOutDate =
Date()' everytime I tried typing the () and I hit enter to input the next
line of code, the parenthesis disappear. So the line ends up looking like
this:dteOutDate = Date

When I am finished with the code, I go into the actual form, after I have
changed the source to read =mthsElapsed([receiveddate],[outdate]), and I get
an ERROR in the area where the total months should dbe displayed. What I did
noticed was that if the Out Date has an actual date, it will go ahead and
calculate the months and display it correctly. But if the Out Date is Blank
it will give me an error, instead of doing the calculation based on the
actual date, since the customer calls and asks 'what are my current charges',
then the computer needs to be able to check the current date against the
received date and display the number of months to date, just like it is doing
with the out date.

Any help is greatly appreciated.

 

[Guest]

Dear Douglas

Can you assist me as far as where I need to code this information: I went
ahead and did this, but I am not sure if this is right, for I am still
getting an error:

Public Function MthsElapsed(dteRecDate As Date, dteOutDate As Date) As Integer

If IsNull(dteOutDate) Then
dteOutDate = Date
End If

MthsElapsed = DateDiff("m", [receiveddate], Nz([outdate], Date))
IIf(Day(Nz([outdate], Date())) < Day([receiveddate]),1,0)
End Function

When I ran it, it said "Compile Error" Syntax Error and it highlights the
IIF line.
Can you help me please?

To access, that IS 1 month, just as it's 1 year between 31 Dec, 2005 and 1
Jan, 2006.

Try using the same sort of calculation that's used to calculate age, where
you need to adjust the age depending on whether or not the birthday has
already occurred in the month:

DateDiff("m", [receiveddate], Nz([outdate], Date())) - _
IIf(Day(Nz([outdate], Date())) < Day([receiveddate]), 1, 0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi
I am having a major diffuclty with this: Look at this formula:
Month Calculation

=(((DateDiff("d",[receiveddate],IIf(IsNull([outdate]),Date(),
outdate])))<1),1,(DateDiff("m",[receiveddate],IIf(IsNull
([outdate]),Date(),[outdate]))))

This formula calcualtes the date difference between the 'received date' and
the 'ship date' if the 'ship date is blank' then the 'current date', then it
tells me how many months have gone by. If the month value is less than one
month, it automatically sets it to one. The problem I am having is this, I
need this formula to calculate a new month, as soon as 30 days have gone by.
For example, if the 'received date' is January 15, 2006 and the 'ship date'
was February 20, 2006, the way the formula is now, it will show me (1) month,
when in actuality should be (2), since the total of days is 37 days. That
means that when the 'current date' was February 14, 2006 it should
automatically changed to (2). Can you modify the formula above to do what I
have described here.

Since then, I have created a new formula:
Public Function MthsElapsed(dteRecDate As Date, dteOutDate As Date)
as Integer

If IsNull(dteOutDate) Then
dteOutDate = Date()
End If

MthsElapsed = DateDiff("m",dteRecDate,dteOutDate)
If Day(dteOutDate) > Day(dteRecDate) Then
MthsElapsed = MthsElapsed + 1
End If
End Function

This formula works half way. First, the line that reads 'dteOutDate =
Date()' everytime I tried typing the () and I hit enter to input the next
line of code, the parenthesis disappear. So the line ends up looking like
this:dteOutDate = Date

When I am finished with the code, I go into the actual form, after I have
changed the source to read =mthsElapsed([receiveddate],[outdate]), and I get
an ERROR in the area where the total months should dbe displayed. What I did
noticed was that if the Out Date has an actual date, it will go ahead and
calculate the months and display it correctly. But if the Out Date is Blank
it will give me an error, instead of doing the calculation based on the
actual date, since the customer calls and asks 'what are my current charges',
then the computer needs to be able to check the current date against the
received date and display the number of months to date, just like it is doing
with the out date.

Any help is greatly appreciated.

 

[Douglas J Steele]

You missed the fact that there was a minus sign at the end of the first
line, following by a space, followed by the line continuation character.
Another way to do it would be to have all of that on one line, not 2: just
put a minus sign between the two halves.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dear Douglas

Can you assist me as far as where I need to code this information: I went
ahead and did this, but I am not sure if this is right, for I am still
getting an error:

Public Function MthsElapsed(dteRecDate As Date, dteOutDate As Date) As Integer

If IsNull(dteOutDate) Then
dteOutDate = Date
End If

MthsElapsed = DateDiff("m", [receiveddate], Nz([outdate], Date))
IIf(Day(Nz([outdate], Date())) < Day([receiveddate]),1,0)
End Function

When I ran it, it said "Compile Error" Syntax Error and it highlights the
IIF line.
Can you help me please?

To access, that IS 1 month, just as it's 1 year between 31 Dec, 2005 and 1
Jan, 2006.

Try using the same sort of calculation that's used to calculate age, where
you need to adjust the age depending on whether or not the birthday has
already occurred in the month:

DateDiff("m", [receiveddate], Nz([outdate], Date())) - _
IIf(Day(Nz([outdate], Date())) < Day([receiveddate]), 1, 0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi
I am having a major diffuclty with this: Look at this formula:
Month Calculation

=(((DateDiff("d",[receiveddate],IIf(IsNull([outdate]),Date(),
outdate])))<1),1,(DateDiff("m",[receiveddate],IIf(IsNull
([outdate]),Date(),[outdate]))))

This formula calcualtes the date difference between the 'received

date'
and

the 'ship date' if the 'ship date is blank' then the 'current date',

then
it

tells me how many months have gone by. If the month value is less than one
month, it automatically sets it to one. The problem I am having is this, I
need this formula to calculate a new month, as soon as 30 days have

gone
by.

For example, if the 'received date' is January 15, 2006 and the 'ship date'
was February 20, 2006, the way the formula is now, it will show me (1) month,
when in actuality should be (2), since the total of days is 37 days. That
means that when the 'current date' was February 14, 2006 it should
automatically changed to (2). Can you modify the formula above to do

what
I

have described here.

Since then, I have created a new formula:
Public Function MthsElapsed(dteRecDate As Date, dteOutDate As Date)
as Integer

If IsNull(dteOutDate) Then
dteOutDate = Date()
End If

MthsElapsed = DateDiff("m",dteRecDate,dteOutDate)
If Day(dteOutDate) > Day(dteRecDate) Then
MthsElapsed = MthsElapsed + 1
End If
End Function

This formula works half way. First, the line that reads 'dteOutDate =
Date()' everytime I tried typing the () and I hit enter to input the next
line of code, the parenthesis disappear. So the line ends up looking like
this:dteOutDate = Date

When I am finished with the code, I go into the actual form, after I have
changed the source to read =mthsElapsed([receiveddate],[outdate]), and

I
get

an ERROR in the area where the total months should dbe displayed. What

I
did

noticed was that if the Out Date has an actual date, it will go ahead and
calculate the months and display it correctly. But if the Out Date is Blank
it will give me an error, instead of doing the calculation based on the
actual date, since the customer calls and asks 'what are my current charges',
then the computer needs to be able to check the current date against the
received date and display the number of months to date, just like it

is
doing

with the out date.

Any help is greatly appreciated.

 

[Guest]

Dear Douglas

I went ahead and modified the formula after I had obmitted the minus sign
and the _ as well as the space. ALthough, I am no longer getting the compile
(syntax) error, I amstill getting an error if the "outdate" has no date, also
the month difference is off by 2 days. For example: If an item was received
on March 01, 2006 and the outdate is March 31, 2006, the number of days is
31. Therefore, the months field should already display (2) months, since it
is a 30 day count. But even so, if I input April 01, 2006, then 32 days have
gone by and it still displays 1 month. It's not until I input April 2, 2006
that the computer calculates 33 days, and then it displays 2 months. Is this
a quick fix? Now, based on this example when the outdate field is blank,
then the dates should be calculated based on today's date, but instead the
month field displays ERROR.
Did I do something wrong?

Sandy

You missed the fact that there was a minus sign at the end of the first
line, following by a space, followed by the line continuation character.
Another way to do it would be to have all of that on one line, not 2: just
put a minus sign between the two halves.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dear Douglas

Can you assist me as far as where I need to code this information: I went
ahead and did this, but I am not sure if this is right, for I am still
getting an error:

Public Function MthsElapsed(dteRecDate As Date, dteOutDate As Date) As Integer

If IsNull(dteOutDate) Then
dteOutDate = Date
End If

MthsElapsed = DateDiff("m", [receiveddate], Nz([outdate], Date))
IIf(Day(Nz([outdate], Date())) < Day([receiveddate]),1,0)
End Function

When I ran it, it said "Compile Error" Syntax Error and it highlights the
IIF line.
Can you help me please?

To access, that IS 1 month, just as it's 1 year between 31 Dec, 2005 and 1
Jan, 2006.

Try using the same sort of calculation that's used to calculate age, where
you need to adjust the age depending on whether or not the birthday has
already occurred in the month:

DateDiff("m", [receiveddate], Nz([outdate], Date())) - _
IIf(Day(Nz([outdate], Date())) < Day([receiveddate]), 1, 0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi
I am having a major diffuclty with this: Look at this formula:
Month Calculation

=(((DateDiff("d",[receiveddate],IIf(IsNull([outdate]),Date(),
outdate])))<1),1,(DateDiff("m",[receiveddate],IIf(IsNull
([outdate]),Date(),[outdate]))))

This formula calcualtes the date difference between the 'received date'
and
the 'ship date' if the 'ship date is blank' then the 'current date', then
it
tells me how many months have gone by. If the month value is less than one
month, it automatically sets it to one. The problem I am having is this, I
need this formula to calculate a new month, as soon as 30 days have gone
by.
For example, if the 'received date' is January 15, 2006 and the 'ship
date'
was February 20, 2006, the way the formula is now, it will show me (1)
month,
when in actuality should be (2), since the total of days is 37 days. That
means that when the 'current date' was February 14, 2006 it should
automatically changed to (2). Can you modify the formula above to do what
I
have described here.

Since then, I have created a new formula:
Public Function MthsElapsed(dteRecDate As Date, dteOutDate As Date)
as Integer

If IsNull(dteOutDate) Then
dteOutDate = Date()
End If

MthsElapsed = DateDiff("m",dteRecDate,dteOutDate)
If Day(dteOutDate) > Day(dteRecDate) Then
MthsElapsed = MthsElapsed + 1
End If
End Function

This formula works half way. First, the line that reads 'dteOutDate =
Date()' everytime I tried typing the () and I hit enter to input the next
line of code, the parenthesis disappear. So the line ends up looking like
this:dteOutDate = Date

When I am finished with the code, I go into the actual form, after I have
changed the source to read =mthsElapsed([receiveddate],[outdate]), and I
get
an ERROR in the area where the total months should dbe displayed. What I
did
noticed was that if the Out Date has an actual date, it will go ahead and
calculate the months and display it correctly. But if the Out Date is
Blank
it will give me an error, instead of doing the calculation based on the
actual date, since the customer calls and asks 'what are my current
charges',
then the computer needs to be able to check the current date against the
received date and display the number of months to date, just like it is
doing
with the out date.

Any help is greatly appreciated.

 

[Guest]

Douglas

Things just got worse. Now if I input a received date of 02/20/06 and an
outdate of 03/01/06, the computer tells me that 11 days have gone by, but now
the months display (O). It doesn't even display 1 or Error. What's going
on??!!

Sandy

You missed the fact that there was a minus sign at the end of the first
line, following by a space, followed by the line continuation character.
Another way to do it would be to have all of that on one line, not 2: just
put a minus sign between the two halves.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dear Douglas

Can you assist me as far as where I need to code this information: I went
ahead and did this, but I am not sure if this is right, for I am still
getting an error:

Public Function MthsElapsed(dteRecDate As Date, dteOutDate As Date) As Integer

If IsNull(dteOutDate) Then
dteOutDate = Date
End If

MthsElapsed = DateDiff("m", [receiveddate], Nz([outdate], Date))
IIf(Day(Nz([outdate], Date())) < Day([receiveddate]),1,0)
End Function

When I ran it, it said "Compile Error" Syntax Error and it highlights the
IIF line.
Can you help me please?

To access, that IS 1 month, just as it's 1 year between 31 Dec, 2005 and 1
Jan, 2006.

Try using the same sort of calculation that's used to calculate age, where
you need to adjust the age depending on whether or not the birthday has
already occurred in the month:

DateDiff("m", [receiveddate], Nz([outdate], Date())) - _
IIf(Day(Nz([outdate], Date())) < Day([receiveddate]), 1, 0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi
I am having a major diffuclty with this: Look at this formula:
Month Calculation

=(((DateDiff("d",[receiveddate],IIf(IsNull([outdate]),Date(),
outdate])))<1),1,(DateDiff("m",[receiveddate],IIf(IsNull
([outdate]),Date(),[outdate]))))

This formula calcualtes the date difference between the 'received date'
and
the 'ship date' if the 'ship date is blank' then the 'current date', then
it
tells me how many months have gone by. If the month value is less than one
month, it automatically sets it to one. The problem I am having is this, I
need this formula to calculate a new month, as soon as 30 days have gone
by.
For example, if the 'received date' is January 15, 2006 and the 'ship
date'
was February 20, 2006, the way the formula is now, it will show me (1)
month,
when in actuality should be (2), since the total of days is 37 days. That
means that when the 'current date' was February 14, 2006 it should
automatically changed to (2). Can you modify the formula above to do what
I
have described here.

Since then, I have created a new formula:
Public Function MthsElapsed(dteRecDate As Date, dteOutDate As Date)
as Integer

If IsNull(dteOutDate) Then
dteOutDate = Date()
End If

MthsElapsed = DateDiff("m",dteRecDate,dteOutDate)
If Day(dteOutDate) > Day(dteRecDate) Then
MthsElapsed = MthsElapsed + 1
End If
End Function

This formula works half way. First, the line that reads 'dteOutDate =
Date()' everytime I tried typing the () and I hit enter to input the next
line of code, the parenthesis disappear. So the line ends up looking like
this:dteOutDate = Date

When I am finished with the code, I go into the actual form, after I have
changed the source to read =mthsElapsed([receiveddate],[outdate]), and I
get
an ERROR in the area where the total months should dbe displayed. What I
did
noticed was that if the Out Date has an actual date, it will go ahead and
calculate the months and display it correctly. But if the Out Date is
Blank
it will give me an error, instead of doing the calculation based on the
actual date, since the customer calls and asks 'what are my current
charges',
then the computer needs to be able to check the current date against the
received date and display the number of months to date, just like it is
doing
with the out date.

Any help is greatly appreciated.

 

[Douglas J Steele]

What's your current code?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas

Things just got worse. Now if I input a received date of 02/20/06 and an
outdate of 03/01/06, the computer tells me that 11 days have gone by, but now
the months display (O). It doesn't even display 1 or Error. What's going
on??!!

Sandy

You missed the fact that there was a minus sign at the end of the first
line, following by a space, followed by the line continuation character.
Another way to do it would be to have all of that on one line, not 2: just
put a minus sign between the two halves.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dear Douglas

Can you assist me as far as where I need to code this information: I went
ahead and did this, but I am not sure if this is right, for I am still
getting an error:

Public Function MthsElapsed(dteRecDate As Date, dteOutDate As Date) As Integer

If IsNull(dteOutDate) Then
dteOutDate = Date
End If

MthsElapsed = DateDiff("m", [receiveddate], Nz([outdate], Date))
IIf(Day(Nz([outdate], Date())) < Day([receiveddate]),1,0)
End Function

When I ran it, it said "Compile Error" Syntax Error and it highlights the
IIF line.
Can you help me please?

:

To access, that IS 1 month, just as it's 1 year between 31 Dec, 2005

and
1

Jan, 2006.

Try using the same sort of calculation that's used to calculate age, where
you need to adjust the age depending on whether or not the birthday has
already occurred in the month:

DateDiff("m", [receiveddate], Nz([outdate], Date())) - _
IIf(Day(Nz([outdate], Date())) < Day([receiveddate]), 1, 0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi
I am having a major diffuclty with this: Look at this formula:
Month Calculation

=(((DateDiff("d",[receiveddate],IIf(IsNull([outdate]),Date(),
outdate])))<1),1,(DateDiff("m",[receiveddate],IIf(IsNull
([outdate]),Date(),[outdate]))))

This formula calcualtes the date difference between the 'received date'
and
the 'ship date' if the 'ship date is blank' then the 'current

date',
then

it
tells me how many months have gone by. If the month value is less

than
one

month, it automatically sets it to one. The problem I am having is this, I
need this formula to calculate a new month, as soon as 30 days

have
gone

by.
For example, if the 'received date' is January 15, 2006 and the 'ship
date'
was February 20, 2006, the way the formula is now, it will show me (1)
month,
when in actuality should be (2), since the total of days is 37

days.
That

means that when the 'current date' was February 14, 2006 it should
automatically changed to (2). Can you modify the formula above to

do
what

I
have described here.

Since then, I have created a new formula:
Public Function MthsElapsed(dteRecDate As Date, dteOutDate As Date)
as Integer

If IsNull(dteOutDate) Then
dteOutDate = Date()
End If

MthsElapsed = DateDiff("m",dteRecDate,dteOutDate)
If Day(dteOutDate) > Day(dteRecDate) Then
MthsElapsed = MthsElapsed + 1
End If
End Function

This formula works half way. First, the line that reads 'dteOutDate =
Date()' everytime I tried typing the () and I hit enter to input

the
next

line of code, the parenthesis disappear. So the line ends up

looking
like

this:dteOutDate = Date

When I am finished with the code, I go into the actual form, after

I
have

changed the source to read =mthsElapsed([receiveddate],[outdate]),

and
I

get
an ERROR in the area where the total months should dbe displayed.

What
I

did
noticed was that if the Out Date has an actual date, it will go

ahead
and

calculate the months and display it correctly. But if the Out Date is
Blank
it will give me an error, instead of doing the calculation based

on
the

actual date, since the customer calls and asks 'what are my current
charges',
then the computer needs to be able to check the current date

against
the

received date and display the number of months to date, just like

it
is

doing
with the out date.

Any help is greatly appreciated.