Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Entering week ending dates | Excel Discussion (Misc queries) | |||
How do I create consecutive dates between a beginning and ending . | Excel Discussion (Misc queries) | |||
Using a col of Dates by day I want to determine a wk ending date. | Excel Worksheet Functions | |||
Using a col of Dates by day I want to determine a wk ending date. | Excel Worksheet Functions | |||
Add new sheets with week ending dates | Excel Programming |