Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

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


Similar Excel Video Tutorials

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 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.


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!!!


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'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!


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


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 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


Hi

I have a holiday planner which is set up with days of month in range c3:ag3.
Name of the staff in b4:b7. Staff will update this with either a 'F'(full
day) or 'H'(half day).

I have all months on the same sheet so feb dates will start in range
c11:ag11 and march will start in c17:ag17 etc

I have set up conditional formatting to highlight the cell depending on the
entry made but I would like if possible is to highlight all cells which are
weekends in grey. Can anyone advise how to do this?

TIA




Each semester I have to calculate what we call a 60% rule for withdrawal from
classes. I figured out the simple part of the formula (start date x .60
(plus the start date for the actual date)

My question is: How can I leave out certain weekends and holidays? I tried
using the WORKDAY or NETWORKDAYS formula without success. This would actually
a day or two on the result in some cases.

The other formula that could use help is the one that adds 3 days or 5 days
to the start date of a class NOT including weekends and holidays.

This is probably easy for most of you, but until now, I have only used Excel
more for information management and not calculations (or only simple ones)

Thank you in advance.



Right now the formula is just taking 5 days before and 5 days after and not taking into account holidays and weekends.

I want to change the formulas in column D and E to 5 workdays/holidays before(column D) and 5 workdays after (column E). The holidays are in Column G.


Hello, I would really appreciate some guidance on how to perform the following task, in excel, for a Six Sigma Green Belt project I am working on.

Background: Goal of project is to reduce cycle time for Quote Requests to a standard of 9 business hours (7-4pm) from receipt of request to response (captured by a Lotus Notes date/time stamp).

Excel Data Structure Current:
Column D "Request Email Date"
Column F "Response Email Date"
Column G "Response Time in Days"
Column H "Response Time in Hours"

Excel Needs:
How would I write a formula to tell me response time per 9 hour day taking in account weekends, non-working hours and, less importantly, Holidays? I've got working formulas in column G and H so I am okay there.

I imagine I'll need to find some sort of Excel calendar for 2006/2007/2008 that lists holidays/weekends and reference it in some way? Sorry I am not much help, I'd say my excel knowledge is more on the beginner side"

Thank you so much in advance for any help you can offer!!

Steve


Hi Excelperts,

I have a column of data with time and date (format: 11/27/2009 12:42:40 PM) with hundreds of rows. I would like to highlight all the cells which fall with in the condition of Monday to Friday and 9:00am to 4:00pm, excluding public holidays. I am bit familiar using conditional formatting, but not sure how to use it for this case. Can anybody help on this?

Regards
potlurul