Calculate within a time range with only start date and end date
 

Is there an excelfunktion (Excel 2007 version) to calculate:
How many employees are on leave of absence on a certain date, when I only
have a start date (first day in a month) in cell G2 and end date (last day in
a month) in cell H2 and the time range can vary from 0,5 to 5 years. I have
a periode of 3 years (2007 to 2009) in which I have to find out how many are
on leave on certain dates f.eks. Feb. 1 2008 and Aug. 1 2009.
Employee 1: jan 07 to jun 07 (result should be 0 on both dates)
Employee 2: jan 07 to dec 09 (result should be 1 on both dates)
Employee 3: mar 08 to mar 10 (result should be 0 on feb. 1 2008 and 1 on
Aug.1 2009)
As I have over 300 employees in the time range, it would take me a long time
to do it manually.
Any help would be appreciated.

--
LEG-denmark

Calculate within a time range with only start date and end date
 

I suspect you could get your results with a Pivot Table. See info. he
http://peltiertech.com/Excel/Pivots/pivottables.htm
http://www.babeled.com/2008/07/18/ex...-manipulation/

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"LEG" wrote:

Is there an excelfunktion (Excel 2007 version) to calculate:
How many employees are on leave of absence on a certain date, when I only
have a start date (first day in a month) in cell G2 and end date (last day in
a month) in cell H2 and the time range can vary from 0,5 to 5 years. I have
a periode of 3 years (2007 to 2009) in which I have to find out how many are
on leave on certain dates f.eks. Feb. 1 2008 and Aug. 1 2009.
Employee 1: jan 07 to jun 07 (result should be 0 on both dates)
Employee 2: jan 07 to dec 09 (result should be 1 on both dates)
Employee 3: mar 08 to mar 10 (result should be 0 on feb. 1 2008 and 1 on
Aug.1 2009)
As I have over 300 employees in the time range, it would take me a long time
to do it manually.
Any help would be appreciated.

--
LEG-denmark

Calculate within a time range with only start date and end date
 

Thanks, I used a pivot table and with some adjusting on the end date I got
what I needed.
LEG-denmark


"LEG" skrev:

Is there an excelfunktion (Excel 2007 version) to calculate:
How many employees are on leave of absence on a certain date, when I only
have a start date (first day in a month) in cell G2 and end date (last day in
a month) in cell H2 and the time range can vary from 0,5 to 5 years. I have
a periode of 3 years (2007 to 2009) in which I have to find out how many are
on leave on certain dates f.eks. Feb. 1 2008 and Aug. 1 2009.
Employee 1: jan 07 to jun 07 (result should be 0 on both dates)
Employee 2: jan 07 to dec 09 (result should be 1 on both dates)
Employee 3: mar 08 to mar 10 (result should be 0 on feb. 1 2008 and 1 on
Aug.1 2009)
As I have over 300 employees in the time range, it would take me a long time
to do it manually.
Any help would be appreciated.

--
LEG-denmark