Create Qtr Ending Dates

[Alex Martinez]

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.

 

[Norman Jones]

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

 

[JE McGimpsey]

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.