Great stuff. I have a question. Suppose your list of names is expanding over time. Can you format the entire column (C in your example) and have the empty boxes remain empty until a value is placed in them without having to reformat as names and dates are added to your list?
Very nice as usual! I did the same for my action list . Please advise how to clear all conditions if the task has been completed, if in some cell in the task row will be "Done"?
Good work! Let's imagine Done is in cell Column C. First cell in C2. Highlight all the cells of the action list starting from row 2. in the formula use =$C2="Done". Choose no formatting - no fill, automatic font etc to turn conditions off.
select cell (can select multiple) which you want to clear conditional format, then go to conditional format then look down for clear format then select clear rules from selected cell.....
Is there any way to do this I have a table with two columns both with dates in. I want to highlight the individual rows if the difference between the two dates is over 1 month. Any help appreciated
Hi Alan, how can I make is so I have a column with net payment terms (i.e NET 45) from the date of the invoice and if the invoice is not paid by the date the report was created it would be red and if it was then green?
Hello, really helpful video, thank you. I have a question. I have a table with some subscription dates, and I wanted to see which subscriptions are going to expire - and it's ok with your 2nd example in this video. So let's say I save the document and close it, and I recheck it after a month. Will the formatted cells remain like that, or they will be updated automatically? If you know, what I mean... Thank you!
Hi there! I am hoping you could help me here as I would need to condition cells with dates less than or equal to today's date to say "Expired" (without creating another column) and at the very same time I want to highlight yellow any cells with dates which are 4 months before today.
Hi Gaga, Please can you help i am trying to figure out how to show dates that older than 6 months from today as red and dates that within 6 months old as green for instance 12/12/21 would be Red where 4/4/23 would be green but i need them to update daily. So hope you can help with this as it is driving me nuts
Thank you. You could use an IF function on that row to test the conditions and show, or not show to data. If you have Excel 365 the FILTER function is an option. Otherwise, VBA to add the rows based on conditions.
Hi I've just subscribed to your channel, and I'm looking at creating 3 columns ( Certificate Date, Due Date & a column that gives me a count down in days to next certificate.....Can You Help :)
I have a large table, and applied conditions to all cells, even cells that data hasn't been entered yet. Is there away to remove my eye sore Expiry highlight conditions for cells without data yet? This has been an awesome tutorial and extremely helpful. Thank you for posting this. I'm going to save it to a private list to review at later dates incase i forget!
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?
Hi Computergaga, How do you highlight 2 or more set of date ranges with common dates. Example is when 2 or more persons applied vacation with date that are common in between.
Thank you I have just started a job as a project coordinator and I wanted a way to check up on outstanding tasks not completed. I watched a video before this one and the process didnt work. I watched your video and all I can say is this is exactly what I wanted. Thank you.
Hi sir thank you so much 😊. could you please provide the expired dates colour and present colour and past colour okkasari antimatic chage chese video kavali please sir
Is there a way to conditional format the entire row based on information in one column? For example, I have a sheet contains a list of events with the date info in one column. Then I conditional format the date column with "=today()" so that all dates passing today will be highlighted. But how do I make sure the entire row, not just the date gets highlighted, is there a way to do this?
Sure. I have a video explaining how here - ua-cam.com/video/uVTk0n277lw/v-deo.html In the example data from this video you would select range A2:C10. Go into Conditional Fromatting and use a formula to determine the formatting. And enter the formula - =$C2
how can I highlight another cell when the date cell is highlighted? Meaning: Date cell C2 is highlighted for expiring. Cell next to date cell “D2” then is highlighted stating “email Jim” for example (white font and white background changes to red background to show the font “email Jim”)
Sure. Let's say the range of cells is from D2:D10 with D1 being a header. Select range D2:D10 and click Home > Conditional Formatting > New Rule > Use a formula to determine the cells to format. Then use the formula =$C2
Great video, could you help me create a formula, where cells that contain dates greater than 30 days of another cell (B2 in my spreadsheet) highlight as orange or red, so I want A2 to be the base cell date, and B2 and C2 to highlight if they contain dates greater than 30 days of cell A2 (base date)
Thank you Leeban. Sure. Select the list of dates in column B and set up a CF rule with this formula - =B2>A2+30 Repeat for column C with this formula. =C2>A2+30
Computergaga still having troubles, but could I ask for another formula if possible, to highlight dates in a column that are 28 days less than Today’s date which for today’s would be 10/03/2020 but whatever today’s date is -28 days for those dates to highlight when they are 28 days or more prior to today’s date
Super helpful video thank you. I have a specific question. I have a column that needs to have four colours. No colour = 2-5 months in advance. Yellow = next month. Green = This month. Red = last month and all other months that pass. Can you help get a formula for the Red colour? Thank you!!
Hi computer gaga, can you teach us how to apply an overlapping conditional format on dates? For the life of me I cannot figure it out For example this scenario : 1. if the date is today I want it to be green background. 2. Then the due date (whichever date it is, referenced towards a table of project with associated due dates) should be red font. 3. IF it's today And it's due, I would like it to be green background and red font. I have tried to apply a AND rule but it doesn't seem to work. Would appreciate any feedback. Thanks.
You can use the new rule format instead of between or higher or lesser than & then select the last option of use a formula to determine which cell to format and it should work out
Depends what you are trying to do, Joe. They can be ignored and errors can be hidden or replaced. But it all depends on why they are there, and what it is that you need to do.
@@Computergaga I have one column of dates where some dates populate and other don't. This is because the task linked to them has not been completed. I'd like to have the CF function only highlight dates greater than a specific date and ignore all blank cells.
If you are using a rule to highlight dates greater than a date, the blanks will not cause an issue. A blank cell has a value of zero so will be less than any date you type.
Hi thank you so much for the Video. It is really very useful. I would appreciate if you can help me with calculating the total number of Year, Months and Days of different assignments.
Excel does not have an ISDATE function so your best bet is to check if a date is within a specific range or after a date. Approach depends on your data.
another option to highlight specific day select dates and then conditional format then new rule then paste =TEXT($C2,"DDD")="SUN" here C2 is reff of starting date cell number
Yes absolutely. There is a NOW function which returns the current date and time. There are also function to extract hours and minutes from a time which could then be used in criteria such as last three hours, just like we can do last three days.
I TRIED IT DID NOT WORKOUT. II KEEP TRYING THE SAME FORMULA OVER AND OVER AGAIN IT DID NOT WORKOUT. I EVEN WENT TO NEW RULE THEN TO CREATE THE FORMULA BUT STILL. WHAT DO YOU THINK IS THE PROBLEM?
I'm not sure how you can automate this Santhosh as every next day would never come. As the date changes, the next day would always be a day away. You would need to log a date in a column somewhere to track it.
I never comment on videos, but this was exactly what I was looking for. Thank you for making my Excel work easier.
You're very welcome. Thank you.
Great stuff. I have a question. Suppose your list of names is expanding over time. Can you format the entire column (C in your example) and have the empty boxes remain empty until a value is placed in them without having to reformat as names and dates are added to your list?
an absolutely amazing video that clearly explained the process. Thank you so much for the time and effort
You're very welcome 😊
Very nice as usual! I did the same for my action list . Please advise how to clear all conditions if the task has been completed, if in some cell in the task row will be "Done"?
Good work!
Let's imagine Done is in cell Column C. First cell in C2. Highlight all the cells of the action list starting from row 2. in the formula use =$C2="Done". Choose no formatting - no fill, automatic font etc to turn conditions off.
Computergaga I will try! Thanks!
select cell (can select multiple) which you want to clear conditional format, then go to conditional format then look down for clear format then select clear rules from selected cell.....
Is there any way to do this
I have a table with two columns both with dates in. I want to highlight the individual rows if the difference between the two dates is over 1 month. Any help appreciated
Hi Alan, how can I make is so I have a column with net payment terms (i.e NET 45) from the date of the invoice and if the invoice is not paid by the date the report was created it would be red and if it was then green?
Hello, really helpful video, thank you. I have a question. I have a table with some subscription dates, and I wanted to see which subscriptions are going to expire - and it's ok with your 2nd example in this video. So let's say I save the document and close it, and I recheck it after a month. Will the formatted cells remain like that, or they will be updated automatically? If you know, what I mean... Thank you!
Hi there!
I am hoping you could help me here as I would need to condition cells with dates less than or equal to today's date to say "Expired" (without creating another column) and at the very same time I want to highlight yellow any cells with dates which are 4 months before today.
I found it very useful, thank you!
Perfect! Just what I looking for. Thank you so much!
Glad it helped!
Crystal clear way of explaining. I luv your videos.
Thank you 😊
Hi Gaga, Please can you help i am trying to figure out how to show dates that older than 6 months from today as red and dates that within 6 months old as green for instance 12/12/21 would be Red where 4/4/23 would be green but i need them to update daily.
So hope you can help with this as it is driving me nuts
From Sheet 1, how do I make a row populate on a different sheet once it has met date conditions??? Great video, Thanks
Thank you. You could use an IF function on that row to test the conditions and show, or not show to data. If you have Excel 365 the FILTER function is an option.
Otherwise, VBA to add the rows based on conditions.
Absolutely brilliant!!! Many thanks
You're very welcome, Zoe.
Saving for future reference! Thanks for sharing!
You're welcome D. Hall.
Very precise and clear. Thank you
You're welcome P Kolosa.
Great video, thank you! I am just curious how you can highlight every other Sunday.
Thank you Martin.
I'm not sure on your question. I'll look into it.
Very professional video, I still cant get mine to work though. I want to highlight red the dates that have expired by 12weeks can you help?
Hi I've just subscribed to your channel, and I'm looking at creating 3 columns ( Certificate Date, Due Date & a column that gives me a count down in days to next certificate.....Can You Help :)
I have a large table, and applied conditions to all cells, even cells that data hasn't been entered yet. Is there away to remove my eye sore Expiry highlight conditions for cells without data yet? This has been an awesome tutorial and extremely helpful. Thank you for posting this. I'm going to save it to a private list to review at later dates incase i forget!
Amazing and simple!
Thank you! 😊
Really very helpful and unique vedio.
Great! Thank you, Brajesh.
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?
If the expire dates start from cell F2, you would use the formula =F2
Life saving! Thank you so much for sharing.
You're very welcome. Thank you.
Hi Computergaga, How do you highlight 2 or more set of date ranges with common dates. Example is when 2 or more persons applied vacation with date that are common in between.
Interesting question. Is this data in a list like the video? Start date in one column and end date in another?
You are so smart Alan. I am really glad that I found you on UA-cam.
Thank you Salim. You're very kind.
Thank you I have just started a job as a project coordinator and I wanted a way to check up on outstanding tasks not completed. I watched a video before this one and the process didnt work. I watched your video and all I can say is this is exactly what I wanted. Thank you.
Excellent! My pleasure, Jenny.
Thank you so much!
You're welcome 👍
im looking to get help for a tracking booking service spreadsheet can you assist
Sorry Peter, I don't have availability for consultancy projects at the moment.
Thanks well illustrated
You're welcome hoggrobinson.
Great tutorial, many thanks Alan. Cheers Mohideen
You're welcome, thank you Mohideen.
Thanks this helped me fix my issue
Great sir! Answered my question in the first 2 minutes!
Great to hear 👍
That's brilliant!! Thanks so much for explaining how to highlight a day of the week!
My pleasure Yariv.
very easy to learn.
Excellent! 👍
Really helpful video, thank you!
You're very welcome Diana, thank you.
Hi sir thank you so much 😊. could you please provide the expired dates colour and present colour and past colour okkasari antimatic chage chese video kavali please sir
You're welcome. I don't know the specific colours used. The video is a few years old.
very nice tutorial
Thank you, Abdul.
Is there a way to conditional format the entire row based on information in one column?
For example, I have a sheet contains a list of events with the date info in one column. Then I conditional format the date column with "=today()" so that all dates passing today will be highlighted. But how do I make sure the entire row, not just the date gets highlighted, is there a way to do this?
Sure. I have a video explaining how here - ua-cam.com/video/uVTk0n277lw/v-deo.html
In the example data from this video you would select range A2:C10. Go into Conditional Fromatting and use a formula to determine the formatting. And enter the formula - =$C2
Thanks for the tutorial, it's really help alot.
Excellent! Happy to help, Reni.
how can I highlight another cell when the date cell is highlighted?
Meaning:
Date cell C2 is highlighted for expiring.
Cell next to date cell “D2” then is highlighted stating “email Jim” for example
(white font and white background changes to red background to show the font “email Jim”)
Sure. Let's say the range of cells is from D2:D10 with D1 being a header. Select range D2:D10 and click Home > Conditional Formatting > New Rule > Use a formula to determine the cells to format.
Then use the formula =$C2
U have done perfect but pls Sir, advise that how v can do the change the color in values or expiring date pls...... v r waiting for it.... thx
Thank you. I'm not sure what you are asking Shaukat. There is an example of dates expiring in the video.
Great video, could you help me create a formula, where cells that contain dates greater than 30 days of another cell (B2 in my spreadsheet) highlight as orange or red, so I want A2 to be the base cell date, and B2 and C2 to highlight if they contain dates greater than 30 days of cell A2 (base date)
Thank you Leeban.
Sure. Select the list of dates in column B and set up a CF rule with this formula - =B2>A2+30
Repeat for column C with this formula.
=C2>A2+30
Computergaga thank you I’ll give it a go on Monday and see how it works!
You're welcome.
Computergaga still having troubles, but could I ask for another formula if possible, to highlight dates in a column that are 28 days less than Today’s date which for today’s would be 10/03/2020 but whatever today’s date is -28 days for those dates to highlight when they are 28 days or more prior to today’s date
@@LeebanDC =$A1
Thank you, as usual very clear simple instruction well explained.
Thank you.
Super helpful video thank you. I have a specific question. I have a column that needs to have four colours. No colour = 2-5 months in advance. Yellow = next month. Green = This month. Red = last month and all other months that pass. Can you help get a formula for the Red colour? Thank you!!
yes it is easily possible with excel
You are AMAZING .Thank you Sir
Thank you. Thank you.
Hi computer gaga, can you teach us how to apply an overlapping conditional format on dates? For the life of me I cannot figure it out For example this scenario : 1. if the date is today I want it to be green background. 2. Then the due date (whichever date it is, referenced towards a table of project with associated due dates) should be red font. 3. IF it's today And it's due, I would like it to be green background and red font.
I have tried to apply a AND rule but it doesn't seem to work. Would appreciate any feedback.
Thanks.
You can use the new rule format instead of between or higher or lesser than & then select the last option of use a formula to determine which cell to format and it should work out
What if there are formula errors or blank cells in the sheet?
Depends what you are trying to do, Joe. They can be ignored and errors can be hidden or replaced. But it all depends on why they are there, and what it is that you need to do.
@@Computergaga I have one column of dates where some dates populate and other don't. This is because the task linked to them has not been completed. I'd like to have the CF function only highlight dates greater than a specific date and ignore all blank cells.
@@Computergaga I suppose one work around is to make multiple CF rules and then prioritize them.
If you are using a rule to highlight dates greater than a date, the blanks will not cause an issue. A blank cell has a value of zero so will be less than any date you type.
Absolutely, with the multiple rules and priority 👍
Is there a way to tell when cell went out a yearly date?
Sorry Clarence I don't understand the question. When a cell contains a value that is not this calendar year?
Computergaga I have a spreadsheet that contains month/day/ years in each cell. They expire in a year. I wanted to track when the certification expires
Hi thank you so much for the Video. It is really very useful. I would appreciate if you can help me with calculating the total number of Year, Months and Days of different assignments.
Keep doing what you do! Really helpful!❤
Thank you, Nicki. Great to hear.
How do I replace all date which are less than 1950-01-01 to .
Replace dates? Do you want to remove them?
Best is to filter them and then delete.
Thanks!!
No problem, Tom.
Why can I not find anyone to give me a tutorial on how to set a date which is a year or two or three in the future.
if only date, how can i do?thanks
Excel does not have an ISDATE function so your best bet is to check if a date is within a specific range or after a date.
Approach depends on your data.
very much helpfull
Thank you Shafiq.
Thank you!
You're welcome. Thank you Neriska.
another option to highlight specific day select dates and then conditional format then new rule then paste =TEXT($C2,"DDD")="SUN" here C2 is reff of starting date cell number
A nice option Ashutosh.
how about with time, can you make time option yesterday, now, tomorrow like this video, i very interesting and wait the solved it, thanks
Yes absolutely. There is a NOW function which returns the current date and time. There are also function to extract hours and minutes from a time which could then be used in criteria such as last three hours, just like we can do last three days.
Good video, wish u would hv done it with icons
Thanks Joe.
I TRIED IT DID NOT WORKOUT. II KEEP TRYING THE SAME FORMULA OVER AND OVER AGAIN IT DID NOT WORKOUT. I EVEN WENT TO NEW RULE THEN TO CREATE THE FORMULA BUT STILL. WHAT DO YOU THINK IS THE PROBLEM?
I don't know. Could be many things. The format of the date maybe.
yes, the format:). thanks
Excellent!
thx
I want to reset cell value to zero every next day... So please help me out in this
I'm not sure how you can automate this Santhosh as every next day would never come. As the date changes, the next day would always be a day away. You would need to log a date in a column somewhere to track it.
Thank you so much for the Video. It is really very useful.
You are most welcome
Thank you!