# Identifying the individual days of the week from a range

#### Red Devil

##### New Member
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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### Juan Pablo González

##### MrExcel MVP
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
Many, many thanks! I am at the limit of my abilities here - but you have shown me the way forward. Thanks a million!

Replies
4
Views
475
Replies
3
Views
517
Replies
15
Views
2K
Replies
0
Views
280
Replies
8
Views
480

1,171,585
Messages
5,876,309
Members
433,193
Latest member

### 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.

### Which adblocker are you using?

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

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