The office automation programs are essential for computers. Due to the large number of tools that they offer to users around the world, being used for different purposes, but mainly for administrative purposes. One of them would be to make an indicator semaphore in Excel with dates.
Microsoft programs, the best
There is a whole universality of office automation programs for computers of any type of operating system, but those that command the list of the most used are those belonging to the Microsoft Office package.
Microsoft Excel is probably one of the most complex programs to use of the entire Microsoft Office suite. Some of its tools require some knowledge of computer science, and other options may not be as complex, although they are difficult to discover.
What can you learn today?
On this occasion, you will have the opportunity to use a particular, attractive and interesting tool for your spreadsheets. It’s about… a traffic light!
No, it’s not a joke. Through Excel, you can structure a traffic light to indicate the status of different elements based on the colors green, yellow and red. It is tremendously useful when it comes to indicating expiration dates of products, mainly, but you can adapt it according to your needs.
Indicator traffic light in Excel
Well, the time has come to start. The first thing to clarify is that, for this case, product expiration dates will be used to carry out this exercise. Through the following Excel formulas, it is recommended that you have a table containing:
- Current date, use the formula = TODAY ().
- Alert days (enter the number of days in which you want to be alerted to the expiration of a product).
- Product (those of your preference).
- Expiration date (you can place it at your convenience).
- Overdue days.
Time to color
Once you have everything structured, it’s time to start give shape to this particular and striking indicator traffic light. To do this, follow the instructions below:
- Locate yourself in the “Status” cell of the first item or product.
- Click on the “Formulas” tab in the Excel ribbon, click on “Logics” and, finally, on the “YES” option.
- In the “Logical_test” section, you will place: C5 (expiration date of the first product)
- In “Valor_si_verdadero” you will place “EXPIRED”
- In “Value_if_false” you will place a few quotes separated by space (that is: “”).
- Click on accept and proceed to drag or copy and paste the formula in the rest of the cells.
- Click on “Conditional format“,” Highlight cell rules “,” Containing text “.
- Write the word “EXPIRED” and proceed to use the custom format to assign a font and background color, in this case, red, to indicate that a product is already expired.
Yellow, to notify expired days
Having the cells that will indicate that a product is expired due to the cells filled with a red color, it is time to format the other cells. To do this, follow these steps:
- In “Days Overdue”, enter the following formula: = A2-C5, to show the days overdue that the product has.
- Before pressing “Enter”, Shade“ A2 ”and press F4.
- Copy and paste the formula to the rest of the boxes.
- Repeat the conditional formatting procedure to assign the color yellow to these cells.
Green, expiration alert!
To finish with the indicator traffic light, it is important to know when some products are about to expire. So, in this section, you will perform the following actions:
- Repeat the procedure of: “Format“,” Logic “,” YES “.
- In “Logical_Test”, you will put: E5 (days expired) = -C2 (alert days, shade C2 and press F4).
- On “Value_if_true”You will place:“ NEXT TO OVERCOME ”.
- Once again, perform the conditional formatting procedure to assign the color of the letter and the background of the green box.
Clever! You already have your traffic light
Now you know how to make an indicator semaphore in Excel with dates. In this way, you will surprise everyone with your knowledge and incredible Excel charts, thanks to this important exercise that will help you keep control, not only of products, but also of different activities.