Get work hours between dates custom schedule

Get work hours between dates custom schedule

Generic formula 

{=SUM(CHOOSE(WEEKDAY(ROW(INDIRECT(date1&":"&date2))),1,2,3,4,5,6,7))}

Related formulas 

Get work hours between dates

Explanation

To calculate work hours between two dates with a custom schedule, you can use a formula based on the WEEKDAY and CHOOSE functions.

In the example shown, the formula in F8 is:

{=SUM(CHOOSE(WEEKDAY(ROW(INDIRECT(B7&":"&C7))),0,8,4,8,4,8,0))}

Which returns 32 hours, based on a custom schedule where 8 hours are worked on Mon, Wed, and Fri, and 4 hours are worked on Tue and Thu.

Note: This is an array formula that must be entered with Control + Shift + Enter.

How this formula works

At the core, this formula uses the WEEKDAY function to figure out the day of week (i.e. Monday, Tuesday, etc.) for every day between the two given dates.

WEEKDAY returns a number between 1 and 7 that corresponds to a particular day of the week. With default settings, 1 = Sunday and 7 = Saturday, so, 2 = Monday, 6 = Friday, and so on.

The trick to this formula is building array of dates that you can feed into the WEEKDAY function. This is done using ROW with INDIRECT like so: 

ROW(INDIRECT(date1&":"&date2))

INDIRECT allows the concatenated dates ""42464:42470"" to be interpreted as row numbers. Then the ROW function returns an array like this:

{42464;42465;42466;42467;42468;42469;42470}

The WEEKDAY function evaluates these numbers as dates and returns this array:

{2;3;4;5;6;7;1}

These numbers correspond to the day of week of each date.

This array is then given to the CHOOSE function as the "index number", which has been configured with 7 values:

value1 - Sunday work hours
value2 - Monday work hours
value3 - Tuesday work hours
value4 - Wednesday work hours
value5 - Thursday work hours
value6 - Friday work hours
value7 - Saturday work hours

Because we are giving CHOOSE more than one index value, it returns more than one result in an array like so:

{8;4;8;4;8;0;0}

These numbers correspond to the hours worked on each day from date1 to date2.

Finally, to get a total, we just need to wrap CHOOSE in SUM and enter as an array formula.

In the example, the final result is 32 hours.

Note: you would think that you could use SUMPRODUCT instead of SUM to avoid the array syntax, but it doesn't work; you still have to use CSE. I know the cause is using INDIRECT with ROW to build the array of index values, but don't know why that requires CSE. If you know why, please leave a comment below!

Get work hours between dates custom schedule

Generic formula 

{=SUM(CHOOSE(WEEKDAY(ROW(INDIRECT(date1&":"&date2))),1,2,3,4,5,6,7))}

Related formulas 

Get work hours between dates

Explanation

To calculate work hours between two dates with a custom schedule, you can use a formula based on the WEEKDAY and CHOOSE functions.

In the example shown, the formula in F8 is:

{=SUM(CHOOSE(WEEKDAY(ROW(INDIRECT(B7&":"&C7))),0,8,4,8,4,8,0))}

Which returns 32 hours, based on a custom schedule where 8 hours are worked on Mon, Wed, and Fri, and 4 hours are worked on Tue and Thu.

Note: This is an array formula that must be entered with Control + Shift + Enter.

How this formula works

At the core, this formula uses the WEEKDAY function to figure out the day of week (i.e. Monday, Tuesday, etc.) for every day between the two given dates.

WEEKDAY returns a number between 1 and 7 that corresponds to a particular day of the week. With default settings, 1 = Sunday and 7 = Saturday, so, 2 = Monday, 6 = Friday, and so on.

The trick to this formula is building array of dates that you can feed into the WEEKDAY function. This is done using ROW with INDIRECT like so: 

ROW(INDIRECT(date1&":"&date2))

INDIRECT allows the concatenated dates ""42464:42470"" to be interpreted as row numbers. Then the ROW function returns an array like this:

{42464;42465;42466;42467;42468;42469;42470}

The WEEKDAY function evaluates these numbers as dates and returns this array:

{2;3;4;5;6;7;1}

These numbers correspond to the day of week of each date.

This array is then given to the CHOOSE function as the "index number", which has been configured with 7 values:

value1 - Sunday work hours
value2 - Monday work hours
value3 - Tuesday work hours
value4 - Wednesday work hours
value5 - Thursday work hours
value6 - Friday work hours
value7 - Saturday work hours

Because we are giving CHOOSE more than one index value, it returns more than one result in an array like so:

{8;4;8;4;8;0;0}

These numbers correspond to the hours worked on each day from date1 to date2.

Finally, to get a total, we just need to wrap CHOOSE in SUM and enter as an array formula.

In the example, the final result is 32 hours.

Note: you would think that you could use SUMPRODUCT instead of SUM to avoid the array syntax, but it doesn't work; you still have to use CSE. I know the cause is using INDIRECT with ROW to build the array of index values, but don't know why that requires CSE. If you know why, please leave a comment below!