Create Qtr Ending Dates
 

Hi,

I work for an insurance company using Excel 2002. I need somehow to input
the following via command button into the cells based on a cell call
inception or expiration date.
For example I have the following fields in two separate cells:

Inception Date Expiration Date
9/30/2005 9/30/2006

What I want is to pick the inception/expiration (don't know what is best to
use) date using a command button to get the following: to a row of cells.

1st Qtr - 12/31/2005
This will be the 1st quarter after 9/30/2005 the second quarter to a new
cell will be:
2nd Qtr - 3/31/2006
The third quarter will be
3rd Qtr - 6/30/2006
The fourth quarter will be
4th Qtr - 9/30/2006

It should all be on a quarterly calendar basis. This is for payroll
purposes. If I get an expiration date that is for example 10/31/2005 I want
to show the first quarter to be: 12/31/2005, second quarter 3/31/2006 and
so on. Can this be done? Thank you in advance.

Create Qtr Ending Dates
 

Hi Alex,

Try:

'=============
Function QtrEnd(Rng As Range) As Date
Dim Yr As Integer
Dim Q1 As Date, Q2 As Date, Q3 As Date, Q4 As Date

If IsDate(Rng.Value) Then
Yr = Year(Rng.Value)
Q1 = DateValue("3/31/" & Yr)
Q2 = DateValue("6/30/" & Yr)
Q3 = DateValue("9/30/" & Yr)
Q4 = DateValue("12/31/" & Yr)

Select Case Rng.Value
Case Is <= Q1: QtrEnd = Q1
Case Is <= Q2: QtrEnd = Q2
Case Is <= Q3: QtrEnd = Q3
Case Else: QtrEnd = Q4
End Select
Else
QtrEnd = CVErr(xlErrNA)
End If

End Function
'<<=============

Worksheet usage:

A1: 10/25/05
B1: =QtrEnd(A1) == 12/31/05


---
Regards,
Norman



"Alex Martinez" wrote in message
...

Hi,

I work for an insurance company using Excel 2002. I need somehow to input
the following via command button into the cells based on a cell call
inception or expiration date.
For example I have the following fields in two separate cells:

Inception Date Expiration Date
9/30/2005 9/30/2006

What I want is to pick the inception/expiration (don't know what is best
to use) date using a command button to get the following: to a row of
cells.

1st Qtr - 12/31/2005
This will be the 1st quarter after 9/30/2005 the second quarter to a new
cell will be:
2nd Qtr - 3/31/2006
The third quarter will be
3rd Qtr - 6/30/2006
The fourth quarter will be
4th Qtr - 9/30/2006

It should all be on a quarterly calendar basis. This is for payroll
purposes. If I get an expiration date that is for example 10/31/2005 I
want to show the first quarter to be: 12/31/2005, second quarter
3/31/2006 and so on. Can this be done? Thank you in advance.

Create Qtr Ending Dates
 

Whether inception or expiration dates is "best to use" depends on what
you're trying to achieve. Assuming the date you pick is in A1, one way:

1st Quarter:
B1: =DATE(YEAR(A1), CEILING(MONTH(A1),3)+1,0)

2nd Quarter:
C1: =DATE(YEAR(B1),MONTH(B1)+4,0)

3rd Quarter
D1: =DATE(YEAR(C1),MONTH(C1)+4,0)

4th Quarter:
=DATE(YEAR(D1),MONTH(D1)+4,0)

If you don't want to display a date until the date in A1 is filled in:

B1: =IF(A1="","",DATE(YEAR(A1), CEILING(MONTH(A1),3)+1,0))
C1: =IF(A1="","",DATE(YEAR(B1),MONTH(B1)+4,0))
etc.


In article ,
"Alex Martinez" wrote:


Hi,

I work for an insurance company using Excel 2002. I need somehow to input
the following via command button into the cells based on a cell call
inception or expiration date.
For example I have the following fields in two separate cells:

Inception Date Expiration Date
9/30/2005 9/30/2006

What I want is to pick the inception/expiration (don't know what is best to
use) date using a command button to get the following: to a row of cells.

1st Qtr - 12/31/2005
This will be the 1st quarter after 9/30/2005 the second quarter to a new
cell will be:
2nd Qtr - 3/31/2006
The third quarter will be
3rd Qtr - 6/30/2006
The fourth quarter will be
4th Qtr - 9/30/2006

It should all be on a quarterly calendar basis. This is for payroll
purposes. If I get an expiration date that is for example 10/31/2005 I want
to show the first quarter to be: 12/31/2005, second quarter 3/31/2006 and
so on. Can this be done? Thank you in advance.