Excel Conditional Formatting with Dates using AND and TODAY Functions
Vložit
- čas přidán 13. 07. 2024
- Conditional Formatting is one of Excel's best features. We use a lot of date fields in Excel. Hire date, purchase order date, invoice date, ship date, and other date fields. We will use Conditional Formatting with dates in this video. we will use Conditional Formatting with the default Date options, and then write a rule using dates. The TODAY function is a great function to use when writing formulas with dates.
Chapters
0:00 Intro
0:15 Today function
1:00 Conditional Formatting - A Date Occurring
2:00 Conditional Formating with Functions
4:50 AND function
5:50 Conditional Formatting with AND and TODAY
#chrismenardtraining #exceltraining #msexcel #conditionalformating #microsoftexcel
And make sure you subscribe to my channel!
- EQUIPMENT USED --------------------------------
○ My camera - amzn.to/3vdgF5E
○ Microphone - amzn.to/3gphDXh
○ Camera tripod - amzn.to/3veN6Rg
○ Studio lights - amzn.to/3vaxyy5
○ Dual monitor mount stand - amzn.to/3vbZSjJ
○ Web camera - amzn.to/2Tg75Sn
○ Shock mount - amzn.to/3g96FGj
○ Boom Arm - amzn.to/3g8cNi6
- SOFTWARE USED --------------------------------
○ Screen recording - Camtasia - chrismenardtraining.com/camtasia
○ Screenshots - Snagit - chrismenardtraining.com/snagit
○ CZcams keyword search - TubeBuddy - www.tubebuddy.com/chrismenard
DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week!
Hi Chris your breakdown was just what I need to able to create a date AND function/rule where if the date was past today's date AND the cell wasn't empty the cell would change read. Spoke very well and slowly done allowing me to colour and rewrite for my work. Big thank you again for your help
Thank you so much sir! 3rd time taking MS-211. 🤞🤞🤞
Excellent content!! Congratulations.
Exactly what I needed!! Thank you!!
You are so welcome!
Hello,
Could you help with a conditional formatting where I need to highlight the bookings for the preceeding week beginning on Sunday? Eg today is Wednesday 11th April 2023 and the highlight should apply for all days after Saturday 15th April or on a Sunday 16th April onwards.
Hello, is there a way to search for cells in multiple sheets of an excel file that contain date within April of 2023 (for example). Thank you
I'm having a heck of a time figuring out how to do something and was curious if you had any ideas. I have a simple spreadsheet. Column A is dates going down by day and column B is a bank account balance for that Rows date. I have a cell on the top of the spreadsheet that I would like to display the balance from column B for whatever the current day is. Again the dates are in column A. Any ideas? Love your content!
Type the TODAY() function and use XLOOKUP in the cell next to it. drive.google.com/file/d/1z_ppi_c_zlBxKdznlNHhHvdZEXU5NEjg/view?usp=drivesdk
Hi Chris, I need your help, how to set automatic title change formula in excel when the date is expired
Do you know if I can evaluate if an excel cell (let's say C3) is "Sat" or "Sun", and if it is, a can use another range of cells to be coloured by light gray?
=IF(OR(C3=”Sat”,C3=”Sun”).
C6:AG15 to be coloured gray?
How would I not use todays date, but a specific date in a cell plus 60 months. I am trying highlight licenses that are about to expire. Thanks!
Hello Chris. Could you please help with a conditional formatting where I need to highlight cells which has date of more than a year ago from today?
Here you go drive.google.com/file/d/1rhfiqlBLF3O4hVypaWN2GUxkLkFYUsFz/view
Hey! I am trying to do this but without the today function , simply just using a date given to me. It does not work though either highlighting everything or nothing is it a problem with my computer or is there a way to fix jt
try use DATE function.
I have the same problem.
I was really hoping this would answer my question. Google isn't helping at all. my end date for my classes are in column E (starting in row 4) and I want column F to say COMPLETED when that date is equal to or less than today's date. Every time I search, it's how to highlight the cells with the dates or how to populate the dates themselves.
Hi Chris...could you please help with a conditional formatting where I need to highlight cells which has date difference (of order date and delivery date) more than 7 days ?
If you want to keep it easy, add a helper column. Do the formula = delivery date - order date. Select the answers and Conditional Formatting, any date greater than 7 highlighted in some color.
Hi Chris! Loved your video! Can you tell what I'm doing wrong in this formula? Trying to highlight a cell based on the word current in one cell and a date within the next 60 days in another. =AND(F2="current",L2=
Two methods. On the left is Conditional formatting. On the right are YEs or NO in column c using the IF function. drive.google.com/file/d/14Nv0XJxlYuufsIR7477nF5xuuMJSS1oq/view?usp=drivesdk
Please help - I have a table with customers, their requests, the delivery deadlines, and a last column is where I have dropdown list with the status of the order - cancelled, delivered ect. So, is there a way to format so that once im over the delivery date, whole row changes colour, but when I set the status to "delivered" it changes to a different colour?
How do you do conditional formatting if i want to add an amount every 1st of the month? i wanted it to be updated every 1st of the month.Thank you
Chris
Any chance you can tell me how we can compare row 1 date row 2 [6digit number] need to find if any match from row3 date and row 4 number[6 digit ]
Plz help how we can do easy way
Really appreciate your help
Thanks
Please use zoomed in picture for demonstration
5:28 5:30 5:31 Hey,
is it possible to make it based on multiple specific dates as example, there are certain days where no one will be at work, so you don't want information send out on those days
Example on formular I'm using but split into multiple conditional formating, one for each date a year where no one will be working:
$L2=DATEVALUE("24-12-2024")
How I'd assume it could look like:
$L2=DATEVALUE("24-12-2024" OR "01-04-2024" OR "09-05-2024)
Is it possible to merge all dates into one conditional formatting? Thank you :)
But what if I want this: show all Case Management Date with =
Hello!! Help please, I created a formula to see if the payment method is ach or check then choose the due date and see if it is greater than a certain date.
If(K5=''Ach'',if(J5>$O$3"Don't Pay","PAY")). The problem I'm running into is when the date equals the same date it'll still say Don't Pay. I tried to reverse the formula and even add an equal sign but it doesnt acknowledge if the dates are the same.