Function Help Please

[Frick]

I have a problem writing a formula where dates are involved.

Col b Col c Col d Col e Col f
row 5 - 1st of Month 4-1-03 5-1-03 6-1-03
row 6 - End of Month 4-31-03 5-31-03 6-31-03

Start End
Date Date
row 10 - Plant 1 10-1-03 12-31-03


This is for a spreadsheet that will show a schedule for the
construction of a plant.

What I am trying to do is create a formula that will yield the letters
"CSD" in the col that corresponds to the start date in row 10 col b
and yield the letters "CFD" in the col that corresponds to the end
date in row 10 col c, and in the 10 months prior to the CSD provide a
negative count down and in the months after the CFD count forward.

Essentially it should look like the following:

6/03 7/03 8/03 9/03 10/03 11/03 12/03 1/04 2/04
3/04 4/04 5/04 6/04 7/04
-4 -3 -2 -1 CSD CFD 1 2
3 4 5 6 7


Any help would be greatly appreciated.

Frick

 

[EarlK]

Frick,

Your example doesn't seem to square with the start and end dates you
provided. Try this:

=IF(MONTH(C4)-MONTH($B$10)<0,MONTH(C4)-MONTH($B$10),IF(C4>$C$10,MONTH(C4)-MO
NTH($C$10),IF(MONTH(C4)=MONTH($B$10),"CSD",IF(MONTH(C4)=MONTH($C$10),"CFD","
"))))

Be sure to format the cells for something numeric, not date.

 

[Sandy Mann]

Frick,

I managed to do what you are asking by the use of hidden rows. It is not a
great solution but it may give you some ideas. Because it uses hidden rows
it will of necessity move your row numbers so, if I may, I will build a
demonstration sheet to illustrate my method. (Also I assume that there
should be a blank call between your CSD and CFD because in November the
plant will still be in the process of being built.)

On a blank sheet enter 1st of Month in Cell B1, 4-1-03 in C1. (To copy the
months along row 1 enter the formula:
=DATE(YEAR(C1),MONTH(C1)+1,1) in cell D1 and drag with the fill handle to
cell P1- forgive me if I am teaching my Granny to suck eggs)

In C7 & C8 enter "Start" & "Date" respectively and similarly in D7 & D8,
"End" & "Date" (or used Alt & Enter to enter the two in the same cell by
wrapping the text - sorry again Granny) In C9 enter the Start date and the
End date in D9.

As we are working only in months, we need to correct the dates to the 1st of
the month . Otherwise, if we test in January to see if a month has passed
since the End date, we will find it hasn't because the End date is the last
day of the previous month. So in C6 enter the formula:
=DATE(YEAR(C9),MONTH(C9),1) and copy along to D6

Now in cell C3 enter the formula:
=DATEDIF(C1,$C$6,"ym") and copy along to P3. You will get #NUM! Errors from
cell H3 onwards but don't worry about them because they will be hidden
later.

In C4 enter the formula:
=DATEDIF($D$6,C1,"ym") and again copy it along ignoring the errors

The next formula to be entered is in cell C5 and is:
=IF(AND(ISERROR(C3),ISERROR(C4)),"",IF(ISERROR(C4),"-"&C3,C4)) and again
copy it along.

Now finally in C2 enter that formula:
=IF(C5="-"&0,"CSD",IF(C5=0,"CFD",C5)) and copy it along the row.

Finally hide rows 4 to 6 and you have the data that you described.

It is possible to combine some of the formula and so have fewer hidden row
but I left it like this for clarity.

HTH

Sandy

 

[Sandy Mann]

Earl,

Unless I am dong something wrong, your fromula produces -9 in the Januay
month (L9), -8 in February etc. which is the problem that I kept running
into when I tried to do it in one formula.

Regards



Sandy

 

[Sandy Mann]

On a blank sheet enter 1st of Month in Cell B1, 4-1-03 in C1. (To copy the
months along row 1 enter the formula:
=DATE(YEAR(C1),MONTH(C1)+1,1) in cell D1 and drag with the fill handle to
cell P1- forgive me if I am teaching my Granny to suck eggs)

Before my Granny replies, if you enter the date in C1, the following month's
date in D1, highlight them both and drag with the fill handle, Excel, which
is more clever than I am, will figure out that you want to index the dates
by months.

Sandy