Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
EDSTAFF
 
Posts: n/a
Default IF/AND/OR/DATEIF Issue...sorry...long post...

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(C260,G260,G26H26),"LATE",DATEDIF(H26,G 26,"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
  #2   Report Post  
EDSTAFF
 
Posts: n/a
Default IF/AND/OR/DATEIF Issue...sorry...long post...

UPDATED FORMULA:
=IF(AND(C260,G260,H260,G26<H26),"LATE",YEAR(G26 )-YEAR(H26)-IF(OR(MONTH(G26)<MONTH(H26),AND(MONTH(G26)=MONTH(H 26),DAY(G26)<DAY(H26))),1,0)&"years,"&MONTH(G26)-MONTH(H26)+IF(AND(MONTH(G26)<=MONTH(H26),DAY(G26)< DAY(H26)),11,IF(AND(MONTH(G26)<MONTH(H26),DAY(G26) =DAY(H26)),12,IF(AND(MONTH(G26)MONTH(H26),DAY(G2 6)<DAY(H26)),-1)))&"months,"&G26-DATE(YEAR(G26),MONTH(G26)-IF(DAY(G26)<DAY(H26),1,0),DAY(H26))&" days")

Formula now correctly performs funciton of calculating renewal date if in
the future and displaying "LATE" if in the past ONLY if data is entered in
columns A-H. I still get #value! in Column I cells if Columns C-H are blank
and can't seem to force it to leave Column I cells blank if corresponding
data in Columsn A-H are blank.

Anyone with any solution?

"EDSTAFF" wrote:

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(C260,G260,G26H26),"LATE",DATEDIF(H26,G 26,"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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
more than 3 conditions in conditional formatting - possible? rob curtis Excel Discussion (Misc queries) 11 August 17th 05 04:02 PM
Date & Time mully New Users to Excel 4 May 23rd 05 11:56 AM
Automatically up date time in a cell Mark Excel Discussion (Misc queries) 5 May 12th 05 12:26 AM
clock Wildman Excel Worksheet Functions 2 April 26th 05 10:31 AM
A few VBA questions - long post! Fiona O'Grady Excel Discussion (Misc queries) 5 December 19th 04 05:12 PM


All times are GMT +1. The time now is 09:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"