The office software are essential for computers . Due to the large number of tools 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.
Learn how to make an indicator semaphore in Excel with dates using conditional formatting. Stay organized and track important events effortlessly
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.
Excel tools
Microsoft Excel is probably one of the most complex programs to use in the entire Microsoft Office suite. Some of its tools require some computer knowledge, 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, in 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 when a product expires).
- Product (those of your preference).
- Expiration date (you can place it at your convenience).
- State.
- Overdue days.
- Alert.
Time to color it
Once you have everything structured, it’s time to start shaping this particular and eye-catching traffic light . To do this, follow the instructions shown 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) <A2 (current date), shade “A2” and press the F4 key.
- In “Valor_si_verdadero” you will place “EXPIRED”
- In “Value_if_false” you will place a few quotes separated by space (ie: “”).
- Click on accept and proceed to drag or copy and paste the formula in the rest of the cells.
- Click on ” Conditional Formatting “, “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 tell you 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).
- In ” Valor_si_verdadero ” you will place: “NEXT TO EXPIRE”.
- Once again, perform the conditional formatting procedure to assign the color of the letter and the background of the green box.
Ready! 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 .
Creating an indicator semaphore in Excel with dates is a powerful way to visually represent important events and track them efficiently. By leveraging Excel’s conditional formatting tool, you can easily apply formatting rules based on specific criteria, making it a valuable tool for data analysis and management.
