Identifying the individual days of the week from a range

Red Devil

New Member
Joined
Oct 14, 2003
Messages
7
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!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
Ok, how about something like this ?
Book3
ABCDEFGH
112345
2GenderDateOutDateInMondayTuesdayWednesdayThursdayFriday
3MAug19,2002Aug30,200222221
4
5
6
7
8
9GenderDataTotal
10MSumofMonday2
11SumofTuesday2
12SumofWednesday2
13SumofThursday2
14SumofFriday1
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

New Member
Joined
Oct 14, 2003
Messages
7
Many, many thanks! I am at the limit of my abilities here - but you have shown me the way forward. Thanks a million!
 

Watch MrExcel Video

Forum statistics

Threads
1,133,243
Messages
5,657,577
Members
418,401
Latest member
B_A_M155

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top