Identifying the individual days of the week from a range

[Red Devil]

I have an excel worksheet which lists all the sick leave taken in the last year (whether two hours, two days, two weeks or two months). I have been asked to identify the days of the week taken and to break it down into male/female and into various work units. There are several thousands of entries.

My problem is when people, take more than one day. The data tells me their first day/date off and their return day/date. I need to be able to identify which days and how many they have taken.

For example, an entry may tell me that Monday 19 August 2002 was the first day of leave and the person returned to work on Friday 30 August (meaning they had 9 days sick leave -- two Mondays, two Tuesdays, two Wednesdays, two Thursdays, one Friday).

The result I need at then end is a chart or graph which shows how many Mondays, Tuesdays etc. have been taken by particular work units.

(The Excel spreadsheet has been provided to me by a colleague using a human resource information system ... my suspicion is that we may need to ask the supplier of that software to show us how to extract what we want ... I am at a loss as to how Excel can do it ... but maybe someone out there knows how!!)

Thanks in anticipation!

 

[Juan Pablo González]

Ok, how about something like this ?

Book3
	A	B	C	D	E	F	G	H
1				1	2	3	4	5
2	Gender	DateOut	DateIn	Monday	Tuesday	Wednesday	Thursday	Friday
3	M	Aug19,2002	Aug30,2002	2	2	2	2	1
4
5
6
7
8
9	Gender	Data	Total
10	M	SumofMonday	2
11		SumofTuesday	2
12		SumofWednesday	2
13		SumofThursday	2
14		SumofFriday	1
Sheet1

The formula in D3 (dragged to the right, and then down) is:

=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,$B3):INDEX(A:A,$C3-1)),2)=D$1))

Then, its only a matter of doing a Pivot Table like the one shown, but with all your other variables, and you're ready !!

 

[Red Devil]

Many, many thanks! I am at the limit of my abilities here - but you have shown me the way forward. Thanks a million!