Changing the Colour of a Date in Microsoft Excel

Colin

Quite Involved in Discussions
#1
I saw a simple but effective technique last week on Excel and I didn't find out how it was done. The company had a training matrix with all the people on 1 axis and all their competencies/qualifications on the other and because some of these were 'time limited' they put down in the corresponding cell the date that particular qualification expired.

In order to avoid getting too close to the expiry date without arranging update training, they set up the spreadsheet to turn the date red 3 months before the expiry date.

Now this is probably quite easy to do but me being a novice in Excel, I don't know how to achieve this - anyone know how?
 
Elsmar Forum Sponsor

Pancho

wikineer
Super Moderator
#3
Re: Changing the colour of a date

It is called "Conditional Formatting".

I have the old Excel 2004 for Mac, and in it the command is in the Format menu. You can enter a condition dependent on the cell value or a formula (i.e. date1-date2 > 90), and the format that applies for TRUE.

 

Solinas

Involved In Discussions
#4
Re: Changing the colour of a date

Conditional formatting is extremely helpful.

In my job, I review extrememly large spreadsheets for Cpk values. You can quickly get blurry eyed. Turn on conditional formatting on a column of Cpk values, highlighting anything with a Cpk less than 2, and you can scan and find them quickly with little effort.

It's also handy for dashboards - color stuff red, yellow, and green.


Mike
 
J

JaneB

#5
Re: Changing the colour of a date

Yup, conditional formatting is wonderful!

IN 07 Excel, it's on the home tab.
 
Thread starter Similar threads Forum Replies Date
S Changing UK Responsible Person UK Medical Device Regulations 0
S Changing PCB Assembler and impact on QMS and MDD CE Certificate EU Medical Device Regulations 2
B How to change hour format to text but not changing the number ? Excel .xls Spreadsheet Templates and Tools 1
A Changing Notified Body EU Medical Device Regulations 6
H Traceability after changing the machine IATF 16949 - Automotive Quality Systems Standard 13
M Changing Investigational Device from Non-Sterile to Sterile - what are the implications? Other Medical Device and Orthopedic Related Topics 3
Q Changing calibration frequency Calibration and Metrology Software and Hardware 18
S Changing revision status of a reviewed document Manufacturing and Related Processes 11
S Sterilization validation after changing sterilization process provider Qualification and Validation (including 21 CFR Part 11) 3
R Impact of changing manufacturer post clinical investigation CE Marking (Conformité Européene) / CB Scheme 3
D Clothes changing in pharmaceutical factory Manufacturing and Related Processes 8
M Changing supplier of critical raw material (III class device) Other Medical Device and Orthopedic Related Topics 1
K Changing the Address in 510(K) Approved Product 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 1
T AS9100 - Changing PEARs target AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 41
C FDA on changing acceptance criteria re: analytical method validation US Food and Drug Administration (FDA) 1
V Statistical basis and justification while comparing / changing sampling plans Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 11
K Changing from Procedure based to Process based Competency Evaluations General Auditing Discussions 5
J Steps when changing material for class 1 medical device 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 1
Marc Email Addresses - Changing your email address Elsmar Xenforo Forum Software Instructions and Help 0
supadrai OEM Changing the name of the manufacturer - length of process? Is 1-3 months correct? Other Medical Device Regulations World-Wide 0
R Changing Document Control software. Must I transfer EVERYTHING? Document Control Systems, Procedures, Forms and Templates 3
A IMDS - Changing the Locked Weight Value RoHS, REACH, ELV, IMDS and Restricted Substances 1
D Changing Templates Approval Field Document Control Systems, Procedures, Forms and Templates 5
P Romer Arm - Profile of a Surface Changing General Measurement Device and Calibration Topics 4
D Changing software classification via software - IEC 62304 IEC 62304 - Medical Device Software Life Cycle Processes 3
L MINITAB 17 - Changing Data Using Minitab Software 3
S Process validation when changing location ISO 13485:2016 - Medical Device Quality Management Systems 10
Don Fardie CAPA vs. Risk Assessment - Changing a product material for better performance ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 2
C Changing Registrars Registrars and Notified Bodies 15
MDD_QNA Changing the Layout of Medical Device Label(s) CE Marking (Conformité Européene) / CB Scheme 3
S Keeping track of the changing Medical Device Standards Other Medical Device Related Standards 3
J Changing of our New Hire Orientation Training Records Training - Internal, External, Online and Distance Learning 12
A Changing a Form Template & Updating Records ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 11
D Is anyone changing their ISO 14001:2004 EMS Policy? ISO 14001:2015 Specific Discussions 8
Q Changing Terminology in ISO 9001:2015 - Documentation Content ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 5
V Changing Indication of Use for a Class IIa Medical Device EU Medical Device Regulations 2
B Changing out Thermocouple - Is new calibration required? General Measurement Device and Calibration Topics 1
F Rules to respect when changing from Certification Body ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 4
MDD_QNA Clinical data requirements - Changing a Class I Medical Device to Class II EU Medical Device Regulations 5
shimonv Changing the Label of a Medical Device product in the field Other US Medical Device Regulations 5
E Information required about changing an off shelf part 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 2
M Changing FMEA template to include Detection Ranking for the PFMEA FMEA and Control Plans 2
WCHorn Changing Sampling Accept Number to Zero, Arbitrarily AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 15
M Amending CFDA Registration for changing site of shipment China Medical Device Regulations 2
S Gage R&R on Potentially Changing Parts - Guidance Needed Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 4
R Re-registration of medical device in Russia - "changing holder" Other Medical Device Regulations World-Wide 1
R Changing CE Mark on Documentation ISO 13485:2016 - Medical Device Quality Management Systems 2
K Changing the Auditor within a Notified Body ISO 13485:2016 - Medical Device Quality Management Systems 4
B Process Capability - Changing Limits to Improve Cpk or find Root Causes? Capability, Accuracy and Stability - Processes, Machines, etc. 8
M Changing Notified Body - Anyone have stories to share CE Marking (Conformité Européene) / CB Scheme 3

Similar threads

Top Bottom