formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to figure out how to do a formula that will calculate the end of
the 3rd, the end of the 6th, and 9th month from a certain date that is
entered. For example, If I have enter the date of 1/15/05 in one field, in
another field I want it to calculate and show 4/30/05 (The last day of the
third month). My original formula only calculates 90 days and will display
something like 4/15/05 and I found out I need to change it to display the
90days but post it as the end of the month so it can be 90days plus so many
other days. The current formula I have is
Apr3 Date: DateAdd("m",3,[_last Effective Date])
(_Last effective date is the name of the field that stores the date they
will enter)

Any ideas on how to do this would be helpful.

Thanks,
Dale
 
Use the DateSerial function.

DateSerial(Year([SomeDate]),Month([SomeDate])+4,0)
will produce 4/30/2006 given any date in January 2006.

DateSerial(Year([SomeDate]),Month([SomeDate])+7,0)
DateSerial(Year([SomeDate]),Month([SomeDate])+10,0)
 
I am trying to figure out how to do a formula that will calculate the end of
the 3rd, the end of the 6th, and 9th month from a certain date that is
entered. For example, If I have enter the date of 1/15/05 in one field, in
another field I want it to calculate and show 4/30/05 (The last day of the
third month). My original formula only calculates 90 days and will display
something like 4/15/05 and I found out I need to change it to display the
90days but post it as the end of the month so it can be 90days plus so many
other days. The current formula I have is
Apr3 Date: DateAdd("m",3,[_last Effective Date])
(_Last effective date is the name of the field that stores the date they
will enter)
Click to expand...

How about:

DateSerial(Year([_last Effective Date]), Month([_last Effective Date])
+ 4, 0)

for three months out? The zeroth day of a month is the last date of
the previous month...

Use 7 and 10 for the sixth and ninth months of course.

John W. Vinson[MVP]
 
Thank you,

It worked great.

John Vinson said:
I am trying to figure out how to do a formula that will calculate the end of
the 3rd, the end of the 6th, and 9th month from a certain date that is
entered. For example, If I have enter the date of 1/15/05 in one field, in
another field I want it to calculate and show 4/30/05 (The last day of the
third month). My original formula only calculates 90 days and will display
something like 4/15/05 and I found out I need to change it to display the
90days but post it as the end of the month so it can be 90days plus so many
other days. The current formula I have is
Apr3 Date: DateAdd("m",3,[_last Effective Date])
(_Last effective date is the name of the field that stores the date they
will enter)
Click to expand...

How about:

DateSerial(Year([_last Effective Date]), Month([_last Effective Date])
+ 4, 0)

for three months out? The zeroth day of a month is the last date of
the previous month...

Use 7 and 10 for the sixth and ninth months of course.

John W. Vinson[MVP]
Click to expand...
 
Thank you. It worked great.

John Spencer said:
Use the DateSerial function.

DateSerial(Year([SomeDate]),Month([SomeDate])+4,0)
will produce 4/30/2006 given any date in January 2006.

DateSerial(Year([SomeDate]),Month([SomeDate])+7,0)
DateSerial(Year([SomeDate]),Month([SomeDate])+10,0)

Dale said:
I am trying to figure out how to do a formula that will calculate the end
of
the 3rd, the end of the 6th, and 9th month from a certain date that is
entered. For example, If I have enter the date of 1/15/05 in one field, in
another field I want it to calculate and show 4/30/05 (The last day of the
third month). My original formula only calculates 90 days and will display
something like 4/15/05 and I found out I need to change it to display the
90days but post it as the end of the month so it can be 90days plus so
many
other days. The current formula I have is
Apr3 Date: DateAdd("m",3,[_last Effective Date])
(_Last effective date is the name of the field that stores the date they
will enter)

Any ideas on how to do this would be helpful.

Thanks,
Dale
Click to expand...
Click to expand...
 
Back
Top