Working with dates in an unusual way?

[AlanN]

I wasn't able to get a solution previously to my problem, so I'm trying again.

I am setting up a simple review schedule for a long term care facility.
Health care compliance requires that:
- 6 weeks after an admission date, a family interview must take place
- continuous quarterly reports are scheduled for the resident 3,6,9 and 12 months from
the admission date. Regardless of the year of the admission date, the quarterly reports will
(should) occur on the month and day based on the initial month and day of
the admission for as long as they are a resident in the facility.

Example
Fields :Col A is admission date; Col B is calculated interview date (=admit date+42 days);Col C is Qrtyly1;Col D is Qrtly2;Col E is Qrtly3;Col F is Annual

AdmitDate InterviewDate Qrtly1 Qrtly2 Qrtly3 Annual
4/2/2004 5/14/2004 Jul. 02 Oct. 02 Jan. 02 Apr. 02


The first 2 columns are actual date fields, the following four fields I want to have text fields- I do not want years in there as they are not relevant.

Can anyone give me a calculation for columns C-F that will present the month and day without treating it as a date?

TIA, AlanN

 

[Bob Phillips]

Alan,

This will work

C2: =TEXT(DATE(YEAR(A2),MONTH(A2)+3,DAY(A2)),"mmm dd")
D2: =TEXT(DATE(YEAR(A2),MONTH(A2)+6,DAY(A2)),"mmm dd")
E2: =TEXT(DATE(YEAR(A2),MONTH(A2)+9,DAY(A2)),"mmm dd")
F2: =TEXT(DATE(YEAR(A2),MONTH(A2)+12,DAY(A2)),"mmm dd")

but beware, if the admission date is 31st Jan, the Qtl1 date retunrs May 01 as April only has 30 days.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I wasn't able to get a solution previously to my problem, so I'm trying again.

I am setting up a simple review schedule for a long term care facility.
Health care compliance requires that:
- 6 weeks after an admission date, a family interview must take place
- continuous quarterly reports are scheduled for the resident 3,6,9 and 12 months from
the admission date. Regardless of the year of the admission date, the quarterly reports will
(should) occur on the month and day based on the initial month and day of
the admission for as long as they are a resident in the facility.

Example
Fields :Col A is admission date; Col B is calculated interview date (=admit date+42 days);Col C is Qrtyly1;Col D is Qrtly2;Col E is Qrtly3;Col F is Annual

AdmitDate InterviewDate Qrtly1 Qrtly2 Qrtly3 Annual
4/2/2004 5/14/2004 Jul. 02 Oct. 02 Jan. 02 Apr. 02


The first 2 columns are actual date fields, the following four fields I want to have text fields- I do not want years in there as they are not relevant.

Can anyone give me a calculation for columns C-F that will present the month and day without treating it as a date?

TIA, AlanN