Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 1 down vote favorite

I have an excel sheet for leave schedule. In Row 1 for example is a merged cell with the month name Jan and in row D3 and E3 are group of date 01-05 and 08-12 and so on.

enter image description here

How can I do this?

share|improve this question
@Dave (and @pnuts): I agree that the question is hard (impossible?) to understand as it stands.  My point is that not everybody has access to a file server where he can publish files that are visible to the world (or they do not know where to find such a server). – Scott Sep 24 '12 at 22:05
sorry if i made everybody confused but how can i attach my sample data. Here is what i wanted to do: I have a leave planner for the staff but instead of doing the highlighting manually i wanted to do it automatically as i enter the start date and the end date. My Calendar is group into a weekday example January 2012 01 -05 and so on.Ex. Start date-15 Jan End Date 30 Jan so it should automatically highlight 15-19 up to 29-31. I hope this is clear enough. Thanks-DatuElijah – Datu Elijah Sep 25 '12 at 5:26
Per meta site "Note you need to be registered member in the site with at least 10 reputation in order to gain the privilege to use this feature." Note that questions such as "how did u add that picture" are supposed to be asked on the meta site. – pnuts Sep 25 '12 at 13:02

closed as not a real question by Dave Rook, Renan, BBlake, Nifle, techie007 Sep 28 '12 at 18:01

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, see the FAQ.

1 Answer

up vote 1 down vote

It is ugly because I think there is probably a better way to achieve the result you want but to answer your question:

  1. Ensure Row2 is formatted as Text and ColumnB and ColumnC as Date.
  2. Select D3.
  3. Styles, Conditional Formatting, New Rule, Use a formula to determine which cells to format.
  4. Copy

=OR(AND(DAY($B3)=VALUE(RIGHT(D$2,2)),DAY($B3)>=VALUE(LEFT(D$2,2))),AND(DAY($C3)>=VALUE(LEFT(D$2,2)),DAY($C3)<=VALUE(RIGHT(D$2,2))))

  1. Paste into Edit the rule Description.
  2. Format values where this formula is true:.
  3. [Set Format as required] Click OK.
  4. Enter =$D:$H into Applies to and click Apply, OK.

You should achieve something like the following:

SU478913 example

where I have shown the result of placing the conditional formula into D3 and copying across & down. This is not required however but serves as a check if the formatting is not what you expected.

Should work for all of ColumnD:H.

I have included the formula in the image as that layout may help in understanding the logic.

share|improve this answer
Sorry got busy at work. Thanks that is what i want but there is one detail that needs to be fix. i was trying to fix it but i run out of time, what if the leave is more that 30 days? lets say 10 jan 2012 up to 31 jan 2012, it will only fill the 08-12 and 29-31 but 15-19 and 22-26 there is no fill. Sorry to bother you again.- thanks in advance – Datu Elijah Sep 27 '12 at 10:25
@DatuElijah You are right -sorry, it is a bug. Would require an even more complex formula and not one I'm prepared to write I'm afraid, partly because I too am busy at work and also because, as I said at the beginning, I suspect what you want to achieve (ie graphical representation of time off) is better done in a significantly different way (like showing each day rather than by week, in a separate sheet, without the Jan header, etc). – pnuts Sep 27 '12 at 11:19
actually i already have the daily one done for next year but my new boss wants it in that grouping because that was done previously by my ex-boss and for him its much easier to easy the date of leave and i am just trying to look if theres way to do it that way by just the conditional formatting or even a macro. Just the same thank you so much for the time you have spend in helping me. If there is really no way to do it automatically i will just going justify the daily schedule i have made. Thanks so much for the help....Datu Elijah – Datu Elijah Sep 27 '12 at 11:43

Not the answer you're looking for? Browse other questions tagged or ask your own question.