calulate holidays taken and remaining on calendar
 

I'm an extreme novice at this stuff so go easy!

I've got a holiday calendar for 6 people in my department at work, where
either 0.5 or 1 day may be booked in a cell for each working day of the year.
Each day has the julian day in the same column.

I need to do a sum for holidays taken (before the julian day has been
reached) and also for those that have been requested but haven't yet taken
place (ie after the julian day) but i'm struggling.

EG. every tuesday in Jan has 1 holiday booked (1 of these has taken place,
the other 3 are still to come. A calculation is required to add these
together.

This will need to be repeated for several people in the below rows on the
same table.

Thanks in advance.
Tom

calulate holidays taken and remaining on calendar
 

=SUMPRODUCT(--($A$1:$IV$1<=TODAY()),A2:IV2)

assuming the dates are in A1:IV1 and one of the people's absence is in
A2:IV2.

Adjust the ranges to suit, and use for leave to go

HTH

Bob

"Tom Graham 185185" wrote in
message ...
I'm an extreme novice at this stuff so go easy!

I've got a holiday calendar for 6 people in my department at work, where
either 0.5 or 1 day may be booked in a cell for each working day of the
year.
Each day has the julian day in the same column.

I need to do a sum for holidays taken (before the julian day has been
reached) and also for those that have been requested but haven't yet taken
place (ie after the julian day) but i'm struggling.

EG. every tuesday in Jan has 1 holiday booked (1 of these has taken place,
the other 3 are still to come. A calculation is required to add these
together.

This will need to be repeated for several people in the below rows on the
same table.

Thanks in advance.
Tom

calculate holidays taken and remaining on calendar
 

Cheers, not quite there yet though.

The 'dates' are just the Julian day (1-31) in cells b14:af14 though. and
rather than compare it to 'TODAY' it's the julian day in cell b1.

the formula i've put in based on your recommendation is
=SUMPRODUCT(--($B$10:$AF$14<=B1),B14:AF14)

still coming up with an error though.

any more clues?

"Bob Phillips" wrote:


=SUMPRODUCT(--($A$1:$IV$1<=TODAY()),A2:IV2)

assuming the dates are in A1:IV1 and one of the people's absence is in
A2:IV2.

Adjust the ranges to suit, and use for leave to go

HTH

Bob

"Tom Graham 185185" wrote in
message ...
I'm an extreme novice at this stuff so go easy!

I've got a holiday calendar for 6 people in my department at work, where
either 0.5 or 1 day may be booked in a cell for each working day of the
year.
Each day has the julian day in the same column.

I need to do a sum for holidays taken (before the julian day has been
reached) and also for those that have been requested but haven't yet taken
place (ie after the julian day) but i'm struggling.

EG. every tuesday in Jan has 1 holiday booked (1 of these has taken place,
the other 3 are still to come. A calculation is required to add these
together.

This will need to be repeated for several people in the below rows on the
same table.

Thanks in advance.
Tom


.

calculate holidays taken and remaining on calendar
 

Ignore that last one, i've corrected the cell reference now!

cheers Bob Phillips

"Tom Graham 185185" wrote:

Cheers, not quite there yet though.

The 'dates' are just the Julian day (1-31) in cells b14:af14 though. and
rather than compare it to 'TODAY' it's the julian day in cell b1.

the formula i've put in based on your recommendation is
=SUMPRODUCT(--($B$10:$AF$14<=B1),B14:AF14)

still coming up with an error though.

any more clues?

"Bob Phillips" wrote:


=SUMPRODUCT(--($A$1:$IV$1<=TODAY()),A2:IV2)

assuming the dates are in A1:IV1 and one of the people's absence is in
A2:IV2.

Adjust the ranges to suit, and use for leave to go

HTH

Bob

"Tom Graham 185185" wrote in
message ...
I'm an extreme novice at this stuff so go easy!

I've got a holiday calendar for 6 people in my department at work, where
either 0.5 or 1 day may be booked in a cell for each working day of the
year.
Each day has the julian day in the same column.

I need to do a sum for holidays taken (before the julian day has been
reached) and also for those that have been requested but haven't yet taken
place (ie after the julian day) but i'm struggling.

EG. every tuesday in Jan has 1 holiday booked (1 of these has taken place,
the other 3 are still to come. A calculation is required to add these
together.

This will need to be repeated for several people in the below rows on the
same table.

Thanks in advance.
Tom


.