Find the Total Hours - Excel Challenge 1

Last week Leslie, one of our readers asked a very interesting question! At the first glance it looked pretty simple but then I got trumped when I deep dived into the question.

This is what she wrote

find-the-total-hours-excel-challenge-3
This weekend I want you to solve this for Leslie! Are you ready?

 

Problem : Find the Total Hours Completed between 5 pm – 7 am

find-the-correct-shift-timing-excel-challenge-2
Let’s say we have this Shift Time Data

 

We need to calculate the total shift hours that fall between 5 pm to 7 am.

For eg :

  1. If the shift starts at 7 am and ends at 5 pm. The total hours clocked (between 5 pm and 7 am) will be 0
  2. If the shift starts at 6 am and ends at 4 pm. The total hours clocked (between 5 pm and 7 am) will be 1
  3. If the shift starts at 4 pm and ends at 10 pm. The total hours clocked (between 5 pm and 7 am) will be 5

 

DOWNLOAD THE SPREADSHEET

The excel also contains manually calculated answers.. Can you help Leslie do it with a formula and not manually?

Make a robust formula to solve this problem and post your answers in the comments !

 

Hungry for More Excel Challenges?

  1. Separate the values in Columns
  2. Find the Shift Timing Challenge
  3. Solve this question without IF (or any other logical function)
  4. Make a Chart from this Data – Charting Challenge

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI