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
This weekend I want you to solve this for Leslie! Are you ready?
Problem : Find the Total Hours Completed between 5 pm – 7 am
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 :
- 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
- 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
- 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
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?
- Separate the values in Columns
- Find the Shift Timing Challenge
- Solve this question without IF (or any other logical function)
- Make a Chart from this Data – Charting Challenge
