I've built a holiday tracker that counts hours taken and calculates remaining
allocations for my staff and for the upcoming holiday year I wanted to add an
additional feature that also tracks what days trhe holiday has been taken on,
but I cant figure out how to collect this information automatically. All the
cells with information in use conditional formatting to change the cell
colour, but I also use dashes to signify a day without hours that still
counts as part of the holiday so I can't use a formula based on cell colour.
What I want is to know how many mondays, tuesdays, wednesdays etc an employee
has taken, so I only need to count the cells with a numberical value in them.

I've put an example in from my tracker below - if anyone can help it would
be greatly appreciated.

P.S. I'm running Excel 2007


5 6 7 8 9 10 11
M T W T F S S
7.50 7.50 7.50 7.50 7.50 - -

7.50 7.50 7.50 7.50 7.50 - -

5.00 5.00

5.50 5.50
- - - - - 5.50 -

This may be useful. It's in days but can easily be adapted to hours.

http://office.microsoft.com/en-us/te...076101033.aspx
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"yimkhoc" wrote:

I've built a holiday tracker that counts hours taken and calculates remaining
allocations for my staff and for the upcoming holiday year I wanted to add an
additional feature that also tracks what days trhe holiday has been taken on,
but I cant figure out how to collect this information automatically. All the
cells with information in use conditional formatting to change the cell
colour, but I also use dashes to signify a day without hours that still
counts as part of the holiday so I can't use a formula based on cell colour.
What I want is to know how many mondays, tuesdays, wednesdays etc an employee
has taken, so I only need to count the cells with a numberical value in them.

I've put an example in from my tracker below - if anyone can help it would
be greatly appreciated.

P.S. I'm running Excel 2007


5 6 7 8 9 10 11
M T W T F S S
7.50 7.50 7.50 7.50 7.50 - -

7.50 7.50 7.50 7.50 7.50 - -

5.00 5.00

5.50 5.50
- - - - - 5.50 -

Hi Russell, thanks for the reply...

Unfortunately it doesn't provide what I need. I'm looking for a formula that
recognises if a cell has a numerical value higher than 0 and counts them. I
can't write a simple countif formula based on a single value as different
staff work different hours on different days. I also mark on the tracker TT
(for Toil Time) and UN (for an authorised unpaid day off), and whilst a
countif works for these two options, I don't know how to cover the hours part
as I want to see how many of each day has been taken as holiday.

Ideally at the end of each month I want to see something like the following:

5 6 7 8 9 10 11 Days worked: m t
w t f s s
m t w t f s s
1 1 1 0 0 0 1
7.5 7.5 7.5 TT UN - 6.5

Any ideas on what formula would work for this?




"Russell Dawson" wrote:

This may be useful. It's in days but can easily be adapted to hours.

http://office.microsoft.com/en-us/te...076101033.aspx
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"yimkhoc" wrote:

I've built a holiday tracker that counts hours taken and calculates remaining
allocations for my staff and for the upcoming holiday year I wanted to add an
additional feature that also tracks what days trhe holiday has been taken on,
but I cant figure out how to collect this information automatically. All the
cells with information in use conditional formatting to change the cell
colour, but I also use dashes to signify a day without hours that still
counts as part of the holiday so I can't use a formula based on cell colour.
What I want is to know how many mondays, tuesdays, wednesdays etc an employee
has taken, so I only need to count the cells with a numberical value in them.

I've put an example in from my tracker below - if anyone can help it would
be greatly appreciated.

P.S. I'm running Excel 2007


5 6 7 8 9 10 11
M T W T F S S
7.50 7.50 7.50 7.50 7.50 - -

7.50 7.50 7.50 7.50 7.50 - -

5.00 5.00

5.50 5.50
- - - - - 5.50 -

Hi Russell, thanks for the reply...

Unfortunately it doesn't provide what I need. I'm looking for a formula that
recognises if a cell has a numerical value higher than 0 and counts them. I
can't write a simple countif formula based on a single value as different
staff work different hours on different days. I also mark on the tracker TT
(for Toil Time) and UN (for an authorised unpaid day off), and whilst a
countif works for these two options, I don't know how to cover the hours part
as I want to see how many of each day has been taken as holiday.

Ideally at the end of each month I want to see something like the following:

5 6 7 8 9 10 11
m t w t f s s
7.5 7.5 7.5 TT UN - 6.5

Days worked: m t w t f s s
1 1 1 0 0 0 1

Any ideas on what formula would work for this?


"Russell Dawson" wrote:

This may be useful. It's in days but can easily be adapted to hours.

http://office.microsoft.com/en-us/te...076101033.aspx
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"yimkhoc" wrote:

I've built a holiday tracker that counts hours taken and calculates remaining
allocations for my staff and for the upcoming holiday year I wanted to add an
additional feature that also tracks what days trhe holiday has been taken on,
but I cant figure out how to collect this information automatically. All the
cells with information in use conditional formatting to change the cell
colour, but I also use dashes to signify a day without hours that still
counts as part of the holiday so I can't use a formula based on cell colour.
What I want is to know how many mondays, tuesdays, wednesdays etc an employee
has taken, so I only need to count the cells with a numberical value in them.

I've put an example in from my tracker below - if anyone can help it would
be greatly appreciated.

P.S. I'm running Excel 2007


5 6 7 8 9 10 11
M T W T F S S
7.50 7.50 7.50 7.50 7.50 - -

7.50 7.50 7.50 7.50 7.50 - -

5.00 5.00

5.50 5.50
- - - - - 5.50 -