Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Automatically Highlight Cells If Weekends Or Holidays

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I'm beginner for excel.
I had been referring many excel example to get what I wanted to.
But still couldn't get the formula I need.
Does anyone can help?
I need the Sheet2 to highlight the cells with condition weekends and holidays.
And highlight with different color when leave is taken in Sheet1.
Is it possible to do so? (with the design I've created)
Thanks

View Answers     

Similar Excel Tutorials

Get a Date that is So Many Working Days Before or After a Date in Excel - WORKDAY
How to find a date that is so many days before or after a specified date, excluding weekends and any holidays. This ...
Get the Number of Workdays Between Two Dates in Excel
How to calculate the total number of working days between two dates in Excel.  This allows you to exclude all weeke ...
Highlight Rows that Meet a Certain Condition in Excel
In this tutorial I am going to cover how to highlight rows that meet a certain condition. To do this I use the Cond ...
Highlight and Sort the Top and Bottom Performers in a List in Excel
How to highlight the rows of the top and bottom performers in a list of data. This allows you to quickly identify ...

Helpful Excel Macros

Highlight Cells which Contain Formulas
- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight
Automatically Highlight the Active or Selected Cell
- This macro will highlight the active cell in any excel spreadsheet with a color selected in the code. This means that a
Highlight Cells with Text or Formulas (non-empty cells)
- This macro will highlight all cells in excel which are not empty. This means that if a cell contains formulas, text, nu
Highlight the Row of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the row of the active cell with a solid
Highlight the Row and Column of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w

Similar Topics







I'm beginner for excel.
I had been referring many excel example to get what I wanted to.
But still couldn't get the formula I need.
Does anyone can help?
I need the Sheet2 to highlight the cells with condition weekends and holidays.
And highlight with different color when leave is taken in Sheet1.
Is it possible to do so?
Thanks


I have a workfile with Dates in column A. The Public Holidays have been set up in column B, but the public holiday dates are also in column A

I need conditional formatting the will highlight the weekend dates as well as the public holidays

I know how to set up conditional formatting to highlight weekends only, but not to incorporate Public Holidays as well. The formula below highlights weekends only. I need it amended so as to highlight public holidays as well.

Howard


=OR(WEEKDAY(A1)=7,WEEKDAY(A1)=1)


Hello All

I have a workbook which is, in essence, a Gantt chart. However I want the weekends and holidays to be greyed out so that it stops people inadvertantly scheduling tasks for those particular days.

I have worked out how to grey out the weekends using Conditional Formating with this formula:

=IF(AND(ISBLANK(E1), OR(WEEKDAY(E$1, 2)=6, WEEKDAY(E$1, 2)=7)), 1, 0)

Anyone have any ideas how I can do the same to grey out public holidays??

MT

I have seen some posts on the above but do not clearly understand how it is working. I have a range of cells in a5:a15 named Holidays. I have time sheets that record from the 1st to the 15th or the 16th to the last day of the month in range D4:S4 and need each column range i.e. D4:D20 to identify weekends and holidays. I got the weekends figured out i.e. =OR(WEEKDAY(D$4)=1,WEEKDAY(D$4)=7). How do I get the cells to indicate another color for the Holidays?


Hi all,
I'm looking for a formula that will give me a string of any 60 business days (to be returned in row 1, columns A to BH) excluding weekends AND holidays.

I have a formula that will exclude weekends:

=if(WEEKDAY(A1)=6,A1+3,if(WEEKDAY(A1)=7,A1+2,A1+1))

And I have a formula that will exclude holidays (but not weekends):

=if(COUNTIF(Holidays,A1),A1+1,A1)

where 'Holidays' is a named range of the 11 market holidays between Christmas '08 and New Year's '10

I'm having trouble combining these such that my formula will exclude both and return only the next 60 'business' dates.

Any help would be greatly appreciated!

Thanks!
FEI7774


Hi all,
I'm looking for a formula that will give me a string of any 60 business days (to be returned in row 1, columns A to BH) excluding weekends AND holidays.

I have a formula that will exclude weekends:

=if(WEEKDAY(A1)=6,A1+3,if(WEEKDAY(A1)=7,A1+2,A1+1))

And I have a formula that will exclude holidays (but not weekends):

=if(COUNTIF(Holidays,A1),A1+1,A1)

where 'Holidays' is a named range of the 11 market holidays between Christmas '08 and New Year's '10

I'm having trouble combining these such that my formula will exclude both and return only the next 60 'business' dates.

Any help would be greatly appreciated!

Thanks!
FEI7774




I am trying to run a macro to put in the Month, Day, Year on each tab but I want it to exclude holidays and weekends. I am not a techie or anything but I would like to know how to do this. I have tried various vb codes but they don't exclude the weekends/holidays. Thanks



I am trying to run a macro to put in the Month, Day, Year on each tab but I want it to exclude holidays and weekends. I am not a techie or anything but I would like to know how to do this. I have tried various vb codes but they don't exclude the weekends/holidays. Thanks

I am creating a Gantt Chart and I am seeking assistance with including Weekends and Holidays as possible work days.

I am using the WORKDAY command to exclude weekends and known holidays; additionally, I am using the WEEKDAY command to evaluate weekends. I am stumped on how to work out a clean way to Include selected Weekend and Holidays as work days.

Any help will be greatly appreciated.




I have been asked to create a strange date calculation in Excel:

Start Date (E21) + Allowable Days (D21) = Finish Date (F21)

But the finish date needs to include weekends and exclude holidays. (I guess they think our employees should be willing to work weekends but holidays are off limits!)

I created a list of the holidays (starting with this month) for the next 3 years in a hidden column (K1:K30).

This current formula in the Finish Date cell works for me but does not take the holidays into account:
=DATE(YEAR(E21), MONTH(E21), DAY(E21) + D21-1)

(The -1 at the end of that formula is because Excel starts counting the day after my Start Date, but I need to count the Start Date as Day 1 in the project schedule.)

Any help on this last step would be greatly appreciated!

Hi there can anyone help me i am an excel beginner but am trying to learn...i have the simple formula =NETWORKDAYS(B2, B3-1, B8:B14)
whre b8:b14 represent holidays. the formula is to calculate the working days between 2 dates and works fine but what i need is to be able to put one date then a number, and excel will give me the date excluding weekends and holidays:
so....B2 would be enter date
B3 would be how many working days (anything between 1-20)
B4 would be the answer to this excluding weekends and holidays?

Many thanks for your help!!!




I found this great template for tracking employees attendance.
My challenge is that the wonderful formula does one thing that I don't want it to do - that is; it does not calculate weekends and holidays. Some of our team work holidays and weekends and they take off for holidays and weekends. How to tell it to count all of the days and subtract them only when they are taken by an employee. I love this set up, it is awesome. I love formulas, but this is above my skills. Thanks for your help.

Please find a copies attached - one is Excel 2007 and one is Excel lower version.

No personal data included - names included but information is made up.


Hello,
I am familiar with excel and the date functions but am unable to get the right equation written. I need to list every workday of the year in the top row of my spreadsheet (no weekends and holidays). I have tried if statements and workday functions with days added and can't seem to get it. Whenever I copy the equation, it returns with weekends and holidays included.

Could you help?
Thanks!


Hi everyone

Slightly strange question here. I need to put dates along the top of a spreadsheet, Cells A1:IV1. I could simply input the starting date in cell A1, then drag the forumla across to IV1. However, I somehow need Excel to NOT list weekends or public holidays.

Does anyone know if this is possible, and if so, how to do it?

Thanks

James


I have a start date in cell A1.
I have a number of days to add to this date in cell A2.
In cell A3 I want to have a formula input a date on these two conditions.

Condition 1 - I want to add the number of days to cell A1 including weekends. But if the date that it comes up with is a Sat or Sun then I need to move the date to the following Monday.

Condition 2 - I want to exclude certain holidays that I choose say up to five. If there is a holiday and it is a weekday and the formula winds up on that date I want it moved to the following day.

Here is what I have come up with so far. But can't figure out how to include holidays.

Cell A1 = 8/1/09
Cell A2 = 8 as in add 8 days
Cell A3 has the following formula.
=IF(WEEKDAY(A1+A2)=7,(A1+A2)+2,IF(WEEKDAY(A1+A2)=1,(A1+A2)+1,A1+A2))

This works for weekends but I want to exclude holidays that might end up on a weekday. I will have holidays listed in cells B1:B5

Thanks
Ken


I'm trying to create a calendar in excel to track my employees leave schedule and i'm sure there's an easy way to do this. Basically in a spreadsheet each tab is a month. The first column starting at line two (A2) has my employee names and it. B1 starts with say 1 Jan and continues until 31 Jan. I'd like to be able to highlight weekends/user defined downdays for each month so that when people build up their leave i can ensure no one is going to work on weekends/down-days. I guess the important thing is there a way to go about highlighting weekends/user-downdays automatically vs. manually? I was trying to create something via a conditional format, but couldn't get any formulas to work out? Any ideas or thoughts would be greatly appreciated!


Can anyone perhaps help with this issue?

I am trying to calculate the difference between 2 dates where 1day = 6hrs, excluding holidays and weekends, e.g. If the start date and end date are the same day the formula needs to return the value of 6.

I have used the formula =(B2-A2)*6+6, but this doesn't exclude weekends and holidays so isn't 100% accurate, see attached my example.

Any help would be highly appreciated.


Is there a way to subtract the day and time in two cells and not include weekends and holidays. The weekends would start at 5:00 p.m. Friday and end at 8:00 am Monday. Holidays would start at 5:00 p.m. the day before the holiday and end on 8:00 am the day after the holiday.

If the holiday falls on a Friday, the time that would be exluded would be from Thursday at 5:00 p.m. to 8:00 a.m. Monday.

If the holiday falls on Monday, the time that would have to be exluded would be 5:00 p.m Friday to 8:00 a.m. Tuesday.

What I am trying to do is below (no weekend example here)

Cell A2 = 08/06/08 2:00 pm
Cell A1 = 08/05/08 1:00 pm
Cell A2 - Cell A1 = 23 hours

Cell A2 = 08/01/2008 4:00 P.m.
Cell A1 - 08/04/2008 9:30 a.m.
Cell A2 - Cell A1 = 2.5 hours (Because this is a weekend.)

The end result would be the number of hours or days with fractions of hours with weekends excluded.

I am trying to use only working business days. I looked at the worksheet functions and couldn't figure out to nest them together. I think this may have to be a program in VBA and my skills don't come anywhere close to this. Right now I am doing it manually and it is taking forever. I am on row 45 of 832.

I am hoping to not have to look up holidays and put them in manually. Maybe Excel could do this??

Thanks for any help!!!


How do I exclude weekends and US holidays from my query without entering all dates into criteria.




I would like to have all holidays and weekends automatically shade to light yellow fill with dark yellow font. All I can seem to manage to do is get the current date to turn green and get the =WEEKDAY function to work. Any help would be greatly appreciated. I have included the spreadsheet below.

Thanks again,

Kurtis Fafard

I have a calendar wizard that will present the calendar for the year that is entered. But I would like to have it highlight in blue those floating federal holidays (MLK Day-3rd Mon. in Jan, Presidents Day-3rd Mon in Feb, Memerial Day-Last Mon. in May, Labor Day-1st Mon. in Sept., Columbus Day, 2nd Mon. in Oct and Thanksgiving Day-4th Thur. in November). I know how to make it highlight the fixed holidays like Christmas and New Years.

Thanks


I would like to calculate the number of hours between 2 dates/times excluding weekends & bank holidays.

For example 27/03/2009 9:00:00 to 30/03/2009 10:00:00 = 25 hours
Or 09/04/2009 9:00:00 to 14/04/2009 10:00:00 = 25 hours (as includes bank holidays)

Is there a formula to do this? I would have a range of cells that lists the bank holidays.


right heres a tricky one.

Ive got a two sets of dates and i need to find out how many days it takes from date a to date b. but here comes the tricky part the days cannot inculde bank holidays or weekends. ther person that did this previous to me had a check box form that asked you to select bank holidays, but weekends were done automatically, I've scanned his macros and can't find a thing of how to do it,

HELP

Difference Actual Difference 22/02/2008 07/04/2008 45 28 02/04/2008 09/04/2008 7 5 31/03/2008 09/04/2008 9 8 06/03/2008 09/04/2008 34 24 12/02/2008 09/04/2008 57 36


Hello,
I would appreciate if someone could help me. I am trying to figure out how to calculate the time that has passed (not including weekends and holidays) between two dates and times. I need a resolution of at least 1/10th of an hour. If the elapsed time calculation is in minutes this would be fine also. The difficult part is I do not want the weekends and holidays included in the calculation. I know about the Networkdays function but haven't been able to get the 1/100th hr (or minutes) resolution that I need. Here is an example of my data and what I hope to calculate:

Start Date/Time: 12/28/04 1:30 AM

End Date/Time: 1/4/05 1:45 PM

Holidays: 12/29/04, 12/30/04

Time elapse calculation: 3 Days (not including weekend & holidays) + 12.25 hrs = 72 hrs + 12.25 hrs = 84.25 hrs (or 5055 minutes)

If anyone knows how to get the above answer with Excel Functions you know your stuff. I have been trying to figure this out for days. I hope someone can help. Thank you!!!

Best regards,
Dan


Hi all,

I have a monthly calender, with each month on its own sheet. I have a sheet named Holidays, which list the holidays. I have been able to use conditional format and highlight the dates in the months the holidays fall on, but I would like to highlight a range of cells below the date on monthly sheet. I have attached a sample of what I have and would like to achieve. I am not much good at using VBA, but would not be against using it either. Thanks for any help.
Steve