| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
CONDITIONAL FORMATTING OR AN 'IF' STATEMENT?
I have a spreadsheet with the following columns:
Col A €“ Date completed Col B €“ Status Col c €“ Scheduled start Date Col D €“ Scheduled Finish Date Information for column c and d is copied weekly into my spreadsheet from another application. I would data entry the dates of the completed work into column A. in the column B €“ status, I want any of the following to appear based on certain criteria €“ ON TIME, OUT OF SPEC, OR OVERDUE for the dates in the Date Completed column. Now the criteria is as follows: the date in Column A is considered on time if it is completed with the date range of the Scheduled Start Date and the Scheduled finish Date + or €“ 5 days on either end. It would be considered Out of Spec if it were completed outside of this range. And if there isnt any date entered in Column A and todays date is past the scheduled finish date, I would like it to show as Overdue. Can anyone help me with this? I am not very experienced with any kind programming. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CONDITIONAL FORMATTING OR AN 'IF' STATEMENT?
Assuming data in row2 down, with real, unambiguous dates
entered/copied into cols A, C and D Place in B2: =IF(AND(A2="",C2="",D2=""),"",IF(AND(A2="",D2<"", TODAY()D2),"Overdue",IF(AND(COUNT(A2,C2:D2)=3,A2 =C2-5,A2<=D2+5),"On Time","Out Of Spec"))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Carla" wrote: I have a spreadsheet with the following columns: Col A €“ Date completed Col B €“ Status Col c €“ Scheduled start Date Col D €“ Scheduled Finish Date Information for column c and d is copied weekly into my spreadsheet from another application. I would data entry the dates of the completed work into column A. in the column B €“ status, I want any of the following to appear based on certain criteria €“ ON TIME, OUT OF SPEC, OR OVERDUE for the dates in the Date Completed column. Now the criteria is as follows: the date in Column A is considered on time if it is completed with the date range of the Scheduled Start Date and the Scheduled finish Date + or €“ 5 days on either end. It would be considered Out of Spec if it were completed outside of this range. And if there isnt any date entered in Column A and todays date is past the scheduled finish date, I would like it to show as Overdue. Can anyone help me with this? I am not very experienced with any kind programming. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CONDITIONAL FORMATTING OR AN 'IF' STATEMENT?
Thanks Max, it works ok with the exception if the Finish Date is in the
future. It is putting Out of Spec in the status cell where it should just be blank. How do I fix this? "Max" wrote: Assuming data in row2 down, with real, unambiguous dates entered/copied into cols A, C and D Place in B2: =IF(AND(A2="",C2="",D2=""),"",IF(AND(A2="",D2<"", TODAY()D2),"Overdue",IF(AND(COUNT(A2,C2:D2)=3,A2 =C2-5,A2<=D2+5),"On Time","Out Of Spec"))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Carla" wrote: I have a spreadsheet with the following columns: Col A €“ Date completed Col B €“ Status Col c €“ Scheduled start Date Col D €“ Scheduled Finish Date Information for column c and d is copied weekly into my spreadsheet from another application. I would data entry the dates of the completed work into column A. in the column B €“ status, I want any of the following to appear based on certain criteria €“ ON TIME, OUT OF SPEC, OR OVERDUE for the dates in the Date Completed column. Now the criteria is as follows: the date in Column A is considered on time if it is completed with the date range of the Scheduled Start Date and the Scheduled finish Date + or €“ 5 days on either end. It would be considered Out of Spec if it were completed outside of this range. And if there isnt any date entered in Column A and todays date is past the scheduled finish date, I would like it to show as Overdue. Can anyone help me with this? I am not very experienced with any kind programming. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CONDITIONAL FORMATTING OR AN 'IF' STATEMENT?
Try this revision in B2, copied down:
=IF(AND(A2="",C2="",D2=""),"",IF(AND(A2="",D2<"", TODAY()<=D2),"",IF(AND(A2="",D2<"",TODAY()D2),"O verdue",IF(AND(COUNT(A2,C2:D2)=3,A2=C2-5,A2<=D2+5),"On Time","Out Of Spec")))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Carla" wrote: Thanks Max, it works ok with the exception if the Finish Date is in the future. It is putting Out of Spec in the status cell where it should just be blank. How do I fix this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I use an if statement within the conditional formatting tool? | Excel Discussion (Misc queries) | |||
| If Statement/Conditional Formatting | Excel Worksheet Functions | |||
| Conditional Formatting Using If Statement On Opening Of Workbook | Excel Worksheet Functions | |||
| =IF Statement formula or conditional formatting | Excel Worksheet Functions | |||
| Conditional formatting based on if statement. | Excel Worksheet Functions | |||
Linear Mode
