formula

[Dale]

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

 

[John Spencer]

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)

 

[John Vinson]

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)

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]

 

[Dale]

Thank you,

It worked great.

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)

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]

 

[Dale]

Thank you. It worked great.

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)

Any ideas on how to do this would be helpful.

Thanks,
Dale