Help With Conditional Formatting

[WiLLBERT]

Hi everyone,

Excel file: http://www.twenty5online.com/Help.xls

I'm trying to help my supervisor out with a spreadsheet he's creating.
Basically, what he wants to be able to do is this:

1. Have Excel automatically highlight the yellow cells dynamically from the
start and end times of the Doe family of employees.

2. Have how many of the Doe family are working every hour dynamically
entered either by comparing the start/end time to the times in the top
columns or by some other manner.

One of these files will be created every day, so it'd be ideal if just the
names and start/end times needed to be updated for the entire spreadsheet to
reflect the new information.

Is this at all possible, or am I insane?

Cheers,
Will

 

[Bryan Hessey]

Select E2
Select E2 to AN7 - making sure that E2 remains the active cell.

Format, Conditional Formatting, Formula is:


=OR(AND($C2<=E$1,$D2>=E$1),AND($C2<=E$1,$D2<$C2),AND($D2>=E$1,$D2<$C2))

and select the Format, Pattern required.

note, 12:00AM in row 4 looks like eithe a very long shift or an error.

Hope this helps

--

 

[WiLLBERT]

Wow! Thanks Bryan, that works great.

Any ideas on how to solve the issue of counting how many people are signed
on that hour?

Cheers,
Will

"Bryan Hessey" <[email protected]>
wrote in message

 

[Bryan Hessey]

Whilst it is possible under VB code to test the Pattern attribute with
ActiveCell.Interior.ColorIndex , I would think that you want to avoid VB
if you can. I will try for a sumproduct test (to amuse Bob) but it is
possible someone else will be able to dissect

=OR(AND($C2<=E$1,$D2>=E$1),AND($C2<=E$1,$D2<$C2),AND($D2>=E$1,$D2<$C2))
into a suitable column count over the range E2 to AN7 before I succeed

more later.

--

 

[Bob Phillips]

Couldn't wait <g>

=SUMPRODUCT((($C2:$C7<=E$1)*($D2:$D7>=E$1))+(($C2:$C7<=E$1)*($D2:$D7<$C2))+(
($D2:$D7>=E$1)*($D2:$D7<$C2)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bryan Hessey" <[email protected]>
wrote in message

 

[Bryan Hessey]

Thanks Bob, had visitor, but couldn't get the split one to work (did yo
guess?).

I still have a bug in the original formula, in that the range of E t
AN covers 36 hours to 12m, so the split range (start 6:00pm finis
3:00am) stops at the first 3:00am but continues after the secon
3:00am.

Might just stop the range colour at midnight.

Cheers

--

 

[Silvabod]

This might help - instead of just using 24 hours, use the full date and time
(you can truncate the display to just read time, if that becomes an issue)
This will give you the "date" diffence between 12.00 on 25/4/2006 and 12.00
on 26/4/2006, thus covering the duplications in 36 hour periods?

"Bryan Hessey" <[email protected]>
wrote in message

 

[Bryan Hessey]

This is true.

The maximum number of people working after midnight might not be enough
to justify adding the full date to the time, however this is WillBert's
choice, to either 'colour' just a 24 hour display and total just a 24
hour count, or as Silvabod suggests, add the date to the time fields)

Thanks Silvabod

--