IF/AND/OR/DATEIF Issue...sorry...long post...

[EDSTAFF]

Have a workbook with muliple linking of sheets.
Sheet 1 is roster with names listed in C11:C60.
Problem is with sheets 2-10 that is various certifications of staff.

On Sheets 2-10,
Column C: Last Name; C11:C60 is linked to Roster (sheet 1) C11:C60 Locked. No issues.
Column D: First Name; D11-D60 is linked to Roster (sheet 1) D11:D60 Locked. No issues.

Column E: Initial Certification Date; Unlocked, for direct entry. No Issues.

Column F: Certification Term (Months) = "24" ONLY if a name is listed in Column C (linked to Roster sheet). This is for cosmetic reasons only so if a name is removed from Roster sheet, the corresponding cell in Column F is blanked.

Expressed as formula: "=IF(C11=0,"","24"),

Locked. No apparent issues. This column will probably be hidden in final version.

Column G: Renewal Date= E+F ONLY if a date is listed in Column E. If Column E is blank, G is also blank (as is Column F by above section.

Expressed as formula: "=IF(E11=0,"",DATE(YEAR(E11),MONTH(E11)+(F11),0))".

Locked. No apparent issues.

Column H: Todays Date: Inserted to calculate time interval between today's date and due date for recertification (Column G). For cosmetic reasons, cell is blanked if no name is present in Column C.

Express as formula: "=IF(C11=0,"",TODAY())

Locked. No apparent issues. Will probably be hidden in final version.

All above formulas "seem" to work properly.

Column I: "Time Until Renewal (Months, Days)" PROBLEM AREA.

What I want this Column I to do is:
If Column C is blank, corresponding Column I cell is also blank. This is for cosmetic reasons so if a name is removed from roster, cell in Column I will be blanked.
If Column C Is NOT blank but Column E is blank, "LATE" will appear. This is to indicate that if person listed in roster can't provide a certification date, it is considered to be outdated.
If Column C is NOT blank AND Column G occurs in the past, "LATE" appears. For obvious reasons, the certification is outdated.
If Column C is NOT blank AND Column G occurs in the future, the calculation is performed to calculate the time in months/days until renewal is due.

Formula for Column I thus far:
"=IF(AND(C26>0,G26>0,G26>H26),"LATE",DATEDIF(H26,G26,"M"))

The problem is:

When C is empty, I get a "#Value!" in Column I corresponding cell when it should be blank.
Dates that occur in the future still get a "LATE" value in Column I if Column C-H contain values. It should calculate the Months/Days until renewal is due.
I also need help changing the "M" so it will calculate months and days until expiration of certification. I have tried "md" but it doesn't calculate what I want.

Thanks for your help,

L.M.

 

[Von Pookie]

Untested, but maybe...?

=IF(C1="","",IF(OR(E1="",G1< TODAY()),"LATE",DATEDIF(H26,G26,"M")))

 

[EDSTAFF]

Untested, but maybe...?

=IF(C1="","",IF(OR(E1="",G1< TODAY()),"LATE",DATEDIF(H26,G26,"M")))

Tried but no success. What should be blank cells in Column I cells gives a "LATE" value. Dates in the future still give a "LATE" value.

 

[GorD]

Try

=IF(C12="","",IF(AND(G12>0,G12<H12),"LATE",DATEDIF(H12,G12,"M")))

this should calculate the time to recert in months only.

Still to try the months and days

See if this works


Edit posted the wrong formula first time -oops

 

[EDSTAFF]

Below is a screen HTML post of excel screenshot. Green colored areas are ok. Red, of course, are the areas in which I am having problems with the formula.

Most is self-explanatory but I'll outline the formulas.

Columns C/D...linked to another sheet No Problems.

Column E: Direct Entry of Certification Date. No Problems.

Column F: Certification Term in Months expressed as formula :
=IF(C13=0,"","24")
Will return a blank entry if no data in Column C. No Problems.

Column G: Renewal Date (E + F), expressed as formula :
=IF(E14=0,"",DATE(YEAR(E14),MONTH(E14)+(F14),0))
Should return a blank if no data in Column E and calculate (E+F) If a date is entered. First part of formula is flawed, second part works correctly. Do not want "#VALUE!" if no date. May correct with a validation Balloon message.


Column H: Today's Date, expressed as formula : =IF(C14=0,"",TODAY())
Should return "blank" if no entry in Column C, return today's date if entry in Column C. No Problems.


Column I: Time until Renewal in Y/M/D. Obtained by calculating difference between Today's date (Column H) and Renewal Date (Column G)Unfortunately, formula is long and system will not allow full post of code. Following is a portion of code: =IF(AND(C14>0,G14>0,G14<H14),"LATE",YEAR(G14)-YEAR(H14)-IF(OR(MONTH(G14)<MONTH(H14),AND(MONTH(G14)=MONTH(H14),DAY(G14)<DAY(H14))),1,0)&"years,"&MONTH(G14)-MONTH(H14)+IF(AND(MONTH(G14)<=MONTH(H14),DAY(G14)<DAY(H14)),11,IF(AND(MONTH(G14)<MONTH(H14),DAY(G14)>=DAY(H14)),12,IF(AND(MONTH(G14)>MONTH(H14),DAY(G14)<DAY(H14)),-1)))&"months,"&G14-DATE(YEAR(G14),MONTH(G14)-IF(DAY(G14)<DAY(H14),1,0),DAY(H14))&" days")

IF/AND statement of formula for Column I specifies that if:
1) Column C is empty, Column I is also empty. Does not work.
2) If Column C has an entry but no date is entered in Column E, it is assumed that certification is outdated and enters LATE in Column I. Does not work as intended.
3) If Column C and E have entries, and Column G is earlier than Column H ( a date in the past), then LATE is entered in Column I. Works correctly.
4) If Column C and E have entries, and Column G is later than Column H (occuring in the future), then the latter portion of the formula calculates the time remaining of the certification in Y/M/D. Works as intended.


Thanks for any help in correcting the formula.

L.M.

Here is the screen shot....

Staff Certifications2.xls
	C	D	E	F	G	H	I	J
10	LAST NAME	FIRST NAME	CERTIFICATION DATE	CERTIFICATION TERM (MONTHS)	RENEWAL DATE	TODAY'S DATE	TIME UNTIL RENEWAL (MONTHS)	DESIRED ENTRY
11
12							#VALUE!	<---BLANK
13	Claus	Santa		24		November 10, 2005	#VALUE!	<<---LATE
14	Poppins	Mary	September-03	24	August 31, 2005	November 10, 2005	LATE	CORRECT
15	Fairy	Tooth	December-04	24	November 30, 2006	November 10, 2005	1years,0months,20 days	CORRECT
16			October-05		#VALUE!		#VALUE!	WILL USE
17							#VALUE!	VALIDATION
18							#VALUE!	BALLOON ON
19							#VALUE!	COLUMN G
20							#VALUE!
21							#VALUE!
22							#VALUE!
ACLS

 

[GorD]

You seem to have done all the hardwork with that mega sized formula. I've just wrapped it in another couple of ifs.

See if this works

Book1.xls
	C	D	E	F	G	H	I	J
9	LAST NAME	FIRST NAME	CERTIFICATION DATE	CERTIFICATION TERM (MONTHS)	RENEWAL DATE	TODAY'S DATE	TIME UNTIL RENEWAL (MONTHS)	DESIRED ENTRY
10
11								<---BLANK
12	Claus	Santa		24		Nov,11,2005	late	<<---LATE
13	Poppins	Mary	Sep-03	24	Aug,31,2005	Nov,11,2005	LATE	CORRECT
14	Fairy	Tooth	1-Dec-04	24	Nov,30,2006	Nov,11,2005	1years,0months,19 days	CORRECT
15			Oct-05		Sep,30,2005			WILL USE
Sheet1

P>S if its not what you want please give some form of feedback rather than just ignoring

 

[GorD]

Oh heres the formula - if you need to post a long formla use the code button above the message input box if you have a lot of < and > signs in a formula the board interprets them as html tags

=IF(C14="","",IF(E14="","late",IF(AND(C14>0,G14>0,G14<H14),"LATE",YEAR(G14)-YEAR(H14)-IF(OR(MONTH(G14)<MONTH(H14),AND(MONTH(G14)=MONTH(H14),DAY(G14)<DAY(H14))),1,0)&"years,"&MONTH(G14)-MONTH(H14)+IF(AND(MONTH(G14)<=MONTH(H14),DAY(G14)<DAY(H14)),11,IF(AND(MONTH(G14)<MONTH(H14),DAY(G14)>=DAY(H14)),12,IF(AND(MONTH(G14)>MONTH(H14),DAY(G14)<DAY(H14)),-1)))&"months,"&G14-DATE(YEAR(G14),MONTH(G14)-IF(DAY(G14)<DAY(H14),1,0),DAY(H14))&" days")))

 

[EDSTAFF]

You seem to have done all the hardwork with that mega sized formula. I've just wrapped it in another couple of ifs.

See if this works

Thanks a bunch GorD!

I made a couple of small changes in your formula and now it seems to work just like I envisioned. This is way beyond my experience and I'm just learning as I go. Someone suggested that I use ISERROR function but this is MUCH easier...especially since I don't know a thing about ISERROR. This saves me a bunce of time and more than a few hairs on me head.

Again, thanks for your help as I was at a dead end.

L.M.

=IF(C11=0,"",IF(E11="","EXPIRED",IF(AND(C11>0,G11>0,G11<H11),"EXPIRED",YEAR(G11)-YEAR(H11)-IF(OR(MONTH(G11)<MONTH(H11),AND(MONTH(G11)=MONTH(H11),DAY(G11)<DAY(H11))),1,0)&"years,"&MONTH(G11)-MONTH(H11)+IF(AND(MONTH(G11)<=MONTH(H11),DAY(G11)<DAY(H11)),11,IF(AND(MONTH(G11)<MONTH(H11),DAY(G11)>=DAY(H11)),12,IF(AND(MONTH(G11)>MONTH(H11),DAY(G11)<DAY(H11)),-1)))&"months,"&G11-DATE(YEAR(G11),MONTH(G11)-IF(DAY(G11)<DAY(H11),1,0),DAY(H11))&" days")))