CODEWITHSUNDEEP
excel
Hazel
Hazel

Reputation: 85

In Excel, how to calculate # of days between a date range, counting only specific days of week

I'm trying to get # days between 2 dates, but had to count only days of the week specified per pair of dates in questions.

For example:

Date of operation = 9/1/2013 and 5/16/2014 Days of the Week in operation = Monday, Wednesday, Friday

I want to calculate total number of days in actual operation, given the days in operation per week.

Upvotes: 2

Views: 2000

Answers (2)

barry houdini
barry houdini

Reputation: 46331

If you have Excel 2010 or later you can use NETWORKDAYS.INTL function which allows you to customise which days are counted, e.g. the following formula will count Mondays, Wednesdays and Fridays between a start date in A2 and an end date in B2 (inclusive) while excluding holiday dates listed in H2:H10

=NETWORKDAYS.INTL(A2,B2,"0101011",H$2:H$10)

The string "0101011" defines the days to include - 0 is included, 1 is excluded - it starts with Monday

In earlier excel versions you can use this formula for the same result

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)))={2,4,6})*(COUNTIF(H$2:H$10,ROW(INDIRECT(A2&":"&B2)))=0))

where {2,4,6} defines the weekdays to include (1=Sun through to 7=Sat)

Upvotes: 6

sbj3
sbj3

Reputation: 217

The number of days between to dates is just date1 - date2.

One method is to divide the number of days in operation by the number of days in the week. In this case that would be 3/7. In this example, the number of days in operation would be 110.14 days. Below is your example. cell B4 has 1-sep-13, b6 has 16-may-14, both formatted as dates. 

    B
4   41518   

6   41775   

8   =B6-B4  Days
9   =B8/7   Weeks
10  =B8*3/7 

    01-Sep-13   

    16-May-14   

    257         Days
    36.71   Weeks
    110.14

To get more accurate, you would need to know which day of the week, weekday(), the first day in your range was and which day of the week your last day was.

Upvotes: 0

Related Questions