Highlight Weekends Dates and Holidays in Excel (Easy Method)

hello and welcome to this excel tips video I am so mad man sir and in this video I'm going to show you how to quickly highlight those cells that have a date which is a weekend date now you can easily do this using conditional formatting and I'm going to cover three things in this video the first one is to highlight those individual cells that have the date which is a weekend date the second one would be to highlight an entire record where the date is the weekend date and the third one is to show you how to also highlight those records where the date is a holiday so you can have a separate holiday calendar and then you can easily highlight those records where the date is a holiday so let's get started here I have this data and I have these dates and these numbers with these dates and I want to quickly highlight all those cells that have a date that is a week ending so in this video I would consider a weekend to be Saturday or Sunday but you can use the same method that I show here to highlight any day it could be only Sunday or it could be any weekly so here let me first show you how I check whether these days are weekend days or not so let me first use the text formula and here will show me the days name so in whether it's a Monday or a Tuesday so here I would use this cell as the reference and the format here would be DDT which means that I just want to check what what's the name of this date and it's Wednesday here and I can copy this for all these cells and you can see that these dates here are Saturdays and Sundays I'm not going to use this formula but I just wanted to show you just to make it clear that these are Saturdays and Sundays it will be easier to follow what the formula that I am going to use is the weekend victim number which means that every day has a number with it and this would help me identify whether a day is a Saturday or a Sunday and that formula is weekday and in this I take the date as the input the serial number and here it gives me all these options how I want to get these numbers so in this case if I choose one or the first option which is the default option it would consider Sunday as one Monday s two and so on I'm going to choose to because in that case it takes Monday as one Tuesday's to Wednesday s 3 and so on so Saturday in Sunday's would be 6 and 7 so here when I hit enter it gives me 3 I can copy it for all these cells expand this away and you can see that Saturday and Sunday would get these numbers which is 6 and 7 so I just want to highlight all those cells where the weekday number is either 6 or 7 now again these two columns is something I'm not going to use while highlighting I'm just keeping these here to show you how these values are and what the formula that I am going to use also let me quickly copy this formula because I'm going to use this in conditional formatting now I would select this entire column I would go to the Home tab and here in conditional formatting I would click on new rule and in the new formatting rule dialog box I would click on use a formula to determine which cells to format and here I would paste the formula here the weekday formula which is weekday a 2 which is this L comma 2 now when this formula and when conditional formatting is going through each cell it is going to check this value so in a two it is going to check weekday a 2 comma 2 for B 2 it sorry a 3 it is going to check weekday a 3 comma 2 and a 4 comma 2 and here I would say that this needs to be greater than 5 because I want these numbers to be 6 or 7 because Saturday is the 6 and Sunday is a 7 now always remember that in this formula here in conditional formatting it should always return a true or a false this is how conditional formatting recognize whether it needs to format a cell or not so if I just leave it as weekday a 2 comma 2 if this is not going to work this formula needs to return a true or false and in this case now it would and Here I am click on format button and then I can apply a formatting in this case it shows you all these options I'm simply going to go with fill and let's say apply an orange color and now when I click OK you can see instantly these cells are highlighted because these are weekend days Saturday and Sundays and that's not for the entire day design so this is a really quick way now what if you do not want to just highlight these cells and what if you want to highlight the entire row so let's see how to do that here again I have the same data and instead of highlighting individual cells I want to highlight the entire row that has a date which is a week ending now for this to work I'm going to use the same concept same conditional formatting formula with a small change in it so here instead of selecting this entire column I'm going to select this entire range where I want to highlight the rows I would go to the Home tab and here in conditional formatting go to new rule and again click on use a formula to determine which cells to format and paste the weekday formula and here instead of using n and this would be greater than 5 of course because this needs to be something that returns a true or false now in the weekday formula as of now it has 8 - and if I just use this formula this is not going to work because when conditional formatting goes to cell a2 it analyzes this formula where the cell reference it's using is a2 but when it moves to B 2 it is going to analyze b2 instead of a2 and I don't want that because my dates are in column a so I want to make sure that column a is somehow locked so I would come here in this formula and I would add a dollar sign before the column alphabet which is e so this makes sure that when it's analyzing each cell in a row it is still referring back to column a which has the date so this entire row would be judged and if the date is a weekend day then it would be highlighted because every cell would return or true and if that day is not a weekend date then it would be false for the entire row and here again I come to format go to fill check this click on this orange color click OK and now when I click OK you can see instantly this entire row has been highlighted based on whether this value this date is a weekend day or not now what if you also want to highlight not just the weekend days but holidays so here I have these holidays here so I have these days say that our holidays and I just don't want to highlight weekend days I also want to highlight holidays so let's see how to do this here I have the data where I've already highlighted rows where the date is a weekend day and I want to check with the holiday calendar and also highlight the dates that the entire record where the date is a holiday and to do that I would use a very simple formula in conditional formatting let me first delete these two columns here and let me show you the formula that would tell me whether this date here is a part of this holiday calendar or not so it would be a really simple lookup formula where this is my lookup value and I go to this holidays tab here and this becomes my lookup area just these numbers these dates and I would press f4 to lock this this is very important because you do not want your reference to change while you're going down the column you want this to be the range that is always referred to when you referring to the holidays so it's important to lock this then the column here would be one because there's just one column in the table array and I'm looking for an exact match so this would be zero and now when I hit enter it gives me this number also I need to make sure that I lock if a two here so in a two I would add a dollar sign before a because I only want to check the date here but I want to highlight this entire row and now when I hit enter again it gives me this number I double click on this and you can see it gives me either a number or not available error and this is because when it finds a date in the holiday tab it would give me the number and if it cannot find that it would give me a not available error but to use a formula and conditional formatting I need to make sure that this returns either true or false so I would come to this formula and I would wrap this vlookup within is number and now when I hit enter it gives me true I can copy it for all these cells and it gives me trues and falses now let me copy this formula and I would use this formula within conditional formatting to highlight all records where the date is a holiday so I would select this entire data set I would go to the Home tab here in conditional formatting click on new rules click on use a formula to determine which cells to format and paste this formula here then click on format and I can choose another color maybe blue click OK and then click again instantly it highlights this row here and this row here because these are the two dates that are holidays so with conditional formatting you can have multiple conditions multiple formulas and it would highlight all those rows all those cells all those records where the formula returns are true so this is how we have highlighted dates that are either weekends or a holiday date that's it in this formula I hope you found this useful also if you're liking these videos please subscribe to this youtube channel and click on the Bell icon so that you never miss out on any new excel tips video I come up with thank you and have a nice day

Channel: TrumpExcel

Views: 54,217

Keywords: highlight weekend dates in excel, highlight weekend in excel, highlight weekends and holidays in excel, highlight weekend columns in excel, highlight weekend rows in excel, highlight weekend cells in excel, highlight weekends in excel gantt chart, highlight holidays in excel, highlight holidays in excel calendar, how to highlight weekends and holidays in excel, advanced excel, excel, excel basics, excel tips, excel tutorials, learn excel, sumit bansal, trump excel

Id: CDkcB8ychHo

Length: 9min 23sec (563 seconds)

Published: Thu Feb 27 2020