Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Calculation Formula- Calendar days minus Holidays | Excel Worksheet Functions | |||
Subtracting Holidays from Calendar Days | Excel Discussion (Misc queries) | |||
how to calulate time | New Users to Excel | |||
Generating business days in a calendar month, EXCLUDING holidays | Excel Worksheet Functions | |||
Generating business days in a calendar month, EXCLUDING holidays | Excel Worksheet Functions |