Essential Skill with Excel: Expiration Alerts with Conditional Formatting

Рет қаралды 264,360

Күн бұрын

Download the file here: s3.amazonaws.com/a2excel.com/...
In this video we'll be learning how to use conditional formatting to create expiration alerts in Excel.
In order to actually get to the conditional formatting, we'll need to know how to use the IF function, the TODAY function, a simple substraction.
The Today function in excel is going to be necessary in order to figure out which dates are due, or nearing due. =TODAY() in excel is dependent on the computer clock.
The =IF() function in excel will figure out, using conditional logic, if we have an expired or non expired invoice. We'll also use a nested if function to add a middle "about to expire" warning.
Finally, we'll be using the Text that Contains conditinal formatting rule to highlight with different colors the cells that are expired or on time.
As an added bonus, we'll use an excel pivot table to count how many invoices are expired.

Пікірлер: 95

@lilakarisma2311 2 жыл бұрын

This video was so helpful!! I used this to organise my assignments and it works like a charm. thank you!

@jasminehang2820 Жыл бұрын

This video was so easy to follow. I have finally found one that I can easily understand and not get confused.

@kalistaflowers5999 2 жыл бұрын

AMAZING VIDEO! Thank you so much for helping. Clear and step by step instructions :)

@officialveronicawrites 2 жыл бұрын

dude thanks so much!! I am doing a work report and this is exactly what I needed! thank you so much, very clear instructions! Subscribed

@rajeshkumarsharma4752 3 жыл бұрын

Very useful ! Use of pivot tables at the end has given a summary.

@nurizzatirossdi6165 3 жыл бұрын

Thank you so much. U make it more easier to understand! I have very limited knowledge with excel since i rarely use before. Done Subscribing your channel. Will refer to your channel to explore more about excel.

@mohamedyusufmohamed6288 3 жыл бұрын

Man thanks for this, you are the best. Your video helped to fix the best document for our maintenance team to not forget all service days in our production.

@mmmm-xm5fc 2 жыл бұрын

After watching this video i believe "NOTHING IS IMPOSSIBLE" Thanks a lot it was really helpful for my project ...

@bxcinderella 3 жыл бұрын

11:37 Noooo, we're not bored at all! Thank you for NOT skipping any steps (however irrelevant) 😊

@theophilusdada1222 3 жыл бұрын

Although the quality of the video is not really clear yet I learnt a lot from it. Thank you very much

@Mahbub_L 3 жыл бұрын

Thank you so much. Is there any possibility if this workbook is in CLOSED mode and it'll send email reminder to the user when a product delivery date about to expire?

@fakdolan 5 ай бұрын

So easy to follow, you're a great teacher, ty!

@fahdiqbal1609 2 жыл бұрын

Learning step by step, love it , thank you

@bvlbvl3331 3 жыл бұрын

Great video but at/around 7:41 seconds, I do not know if it just my computer but it is hard to clearly see the formula written in the formula bar. It would be great if that could be copied/pasted - it is the part showing the nested if

@dbhakimi_.5123 Жыл бұрын

This video was very helpful for my internship project. Thank you very much!

@sagarabrian2882 2 жыл бұрын

You my friend are a life saver. Thanks!!

@van6281 3 жыл бұрын

Your explanation is really good!!! Thank u so much

@IXxfranCxXl 3 жыл бұрын

Thank you! This is so helpful!

@raheelkhokhar46 Жыл бұрын

Great Sir, What a beautiful and detailed explanation, love it. Thanks

@LarPam Жыл бұрын

Thank you for makinf it look so clear and easy!

@narmididi6336 Жыл бұрын

Hello bro, this is very useful and it definitely very helpful for work. You're good! But next thing, after having all the dates, when i used MATCH function to search for a particular date to filter, I cannot do so as the date cells which were calculated with the formula is "packed" with formulas and i think excel MATCH function cannot recognise the value inside to return... are you able to assist pls? Thanks.

@Odawg421 7 ай бұрын

Great video. This is getting me closer to what I’m looking for. In this example that you’ve created, is there a ways to calculate the remaining days excluding weekends and/or holidays?

@melbertseraspe4301 2 жыл бұрын

Thankss for this video learned a lot. However I have a question if how do you make an excel formula to know the expiration of the items you bought 2-3 months and then you want to monitor their expiration lets say 6 months expiration from the month you bought the items.?

@bilelk2388 3 жыл бұрын

Thank you ! Can we do that in power bi ?

@watajs1430 3 жыл бұрын

Amazing and very easy to understand, Thank u so much...

@rashidrizvi2000 Жыл бұрын

Superb!!! You have made my life easy. Subscribed 👍

@sabinocasas9295 2 жыл бұрын

this is a great video, a couple of questions, let's say, once you get the 3 day warning, how can i have excel notify me via outlook? also, how can I send a reminder to several or many people that they need to send out the invoices (in this case) for the ones that have the 3 day warning only? thanks very much in advance!

@AbdulrahmanAlahdal-lc3wz Жыл бұрын

Thank you so much the video was really helpful and explained it in a very clear and easy way.

@rahulpawar8130 2 жыл бұрын

Thanks a ton man...🤗 Its a great help 💐

@christoperharis2468 3 жыл бұрын

Could i change the conditional formatting with icon set ? Could you demonstrate it please ?

@ishtarshehera 3 жыл бұрын

amazing!!!!!!!!!!! best tutorial ever!!!

@hakiinmohamud9852 6 ай бұрын

Thank you so much. Great Video!

@jonatancohen 9 ай бұрын

Amazing video bro. Thanks. Very clear

@chukwunonsoamaikwu9621 3 жыл бұрын

Thank you very much, helped me a lot.

@jasongochez1140 3 жыл бұрын

I know this is a couple of years old but this helped immensely. Thank you very much.

@venkateshwaran666 2 жыл бұрын

Very good Explanations.. Thanks

@vinaynagaonkar8592 Жыл бұрын

Its a wonderful tool, Thank you very much, God Bless You

@786NOUSHAD Жыл бұрын

Thank you so much for you training your so kind and its very easy to understand

@rajeevgosavi4514 3 жыл бұрын

Good Formula. However would appreciate if you explain using zoom , as at some points can't figure out.

@MyLifeWithAI Жыл бұрын

Thanks for that video - it is useful. My question is is Today automatically updated - for example -if I open the file tomorrow - is it going to present tomorrow's date automatically without doing anything on that filed?

@munyaradzinherera3383 4 жыл бұрын

A great presentation

@melleaidandeguzman2584 2 жыл бұрын

can i connect this to a calendar? (with the list of my due assignments). btw thanks! great vid

@soethida5401 2 жыл бұрын

it is very useful for me. thank you so much.

@COCOCOCO-bp7vn 2 ай бұрын

Great job.Thank you

@bluephoenix9 2 жыл бұрын

Cool video. Thanks.

@jeanyosita3537 3 жыл бұрын

super duper thank you so much :)

@muhammadsyahirnorazman2037 3 жыл бұрын

This is very helpful! You got my like and sucsribe.

@st3kilho 3 жыл бұрын

Thanks helped me a lot

@noobstationgaming6733 4 жыл бұрын

you are the best

@CGAZ66 Жыл бұрын

how would you do a start dates that may be different then each other and a end date that is different then each other?

@mosestimothy673 Жыл бұрын

Thanks…It was helpful

@nickynicku9795 2 жыл бұрын

Thank you!

@ragsyrazor 3 жыл бұрын

Best video!!!!!!!!!!

@brendamichieka3743 3 жыл бұрын

this was very useful

@monamidog 2 жыл бұрын

So clear and the explanation is also on point, thanks a lot.

@husamaineia795 Жыл бұрын

thanks a lot...Thant's helped me to much

@SenthilKumar-xu9fb 7 ай бұрын

This Video is very useful. I have try for using this formula for maintaining chemical expiry monitoring. is is fine and working good. Thanks

@credencehomehealthservices 8 ай бұрын

THANK YOU

@zakarialaghrissi9342 3 жыл бұрын

it was useful thank you

@fokhrulabedin Жыл бұрын

Hello, I have a list of product, those have different different expire dates. I want my product status cell will be red color after a certain product has expired. Which formula should i use in conditional formatting?

@observer8291 Жыл бұрын

Thank you very much

@WenboCanadaImmigration Жыл бұрын

thanks a lot

@imransiyal801 Жыл бұрын

Well Done Sir.

@ab-bc2gr Жыл бұрын

How can I set an alert to notify me (via pop-up message) when I open an excel file on: The day before the 3rd Friday of March, June, Sept and Dec of any particular year ?

@sayemhasan3138 4 жыл бұрын

THANK S BROTHER

@rashidrizvi2000 Жыл бұрын

Hi there! How can we insert a row with data after each row as I need to do it manually and print it with Emplyee name, DOJ, Salary for each and every employee. It takes lot of time to copy n paste same row for each employee. Pls help.

@dedy4862 3 жыл бұрын

Thank you

@CGAZ66 Жыл бұрын

question ........does the today date change with the actual date per day???

@user-sc8mq7og4d Жыл бұрын

Useful video

@rogerkd89 3 жыл бұрын

wonderful

@darshanawajira3826 Жыл бұрын

Thanks

@mohammedafeef7301 2 жыл бұрын

Great ...how to send email alerts before expiry let's say before 15 days to my email saying this is going to expire soon...THANK YOU

@KingLouie91 Жыл бұрын

Could you add a nested if to ignore blank cells? For example: =ALS(G7

@dragontamerdragonking9215 Жыл бұрын

Please help I’m trying to add a 4th option “Completed” but I’m receiving an error… I do not want all to show expired.

@pandharinathjoshi6565 Жыл бұрын

Superb sir

@HMVGARAGE 3 жыл бұрын

thanks

@neelprasanna6648 Жыл бұрын

after charging the warning date the 'about to expire' it is not change to expire. How it happen? Formulas and every thing is ok

@user-rx3ww3xi3h 5 ай бұрын

is todays date update automatically day by day?

@richardbelliveau8881 Жыл бұрын

Im trying to set it up so when i enter a date.....i have a warning at 330 days ( yellow) and another warning at 360 days( red). How do i set that up?

@trentorhu7587 Жыл бұрын

Hi. Will this work on Google Sheets?

@CryptoAirDrop.0 Жыл бұрын

Can it give me notification reminder

@JacobBogovefa-bb4os Жыл бұрын

i like it, really helpful however its not clear enough to see.

@ekaetteasuquo5265 3 жыл бұрын

Expand please next time struggling to see it

@rajasekaransundararaj6975 2 жыл бұрын

Sir, I simply say Thanks though I could not follow you. It is difficult to see your display as you have not made the screen 'bigger' like all others YT videos. Need reproduce in zoomed screen please.

@jozefchromy8044 5 ай бұрын

thumb up for the tutorial, but to join you, you would have to learn how to zoom your tutorial - think about you viewers man, after all not everyone has a 20/20 vision and then it becomes a struggle to watch. Some tutorials pay great attention to show, up close the viewer, what they want to convey to them that is kudo for them but this......... Greetings