Sleep duration in 24 h format

Dear people!

For a psychological survey I am trying to calculate the sleep duration of people.
My problem is that it doesn't work if the sleep end is a smaller number than the sleep onset. For example a sleep duration from 22.00 - 08.00.
I was planning to use a fictional date with my formula in the end being "=IF(C3<B3;(A3+1+C3)-(A3+B3);(C3-B3))" as you can see in the attachment.
Can anyone see what the problem is? I'm clueless...

Thanks in advance and best regards

Winston from Hamburg, Germany

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

Your formula should work, but it could be shorter

Yours:

Formula:

Please Login or Register  to view this content.

Mine:

Formula:

Please Login or Register  to view this content.

The problem is that not all the times in column C are true times. They are all right aligned but that is due to the formatting, not because they are numeric. Easy way is to select each cell and press F2 and Enter. Or you can use Text to Columns.

Let me know how the below works for you:

Formula:

Please Login or Register  to view this content.

To be copied into cell D10

This seems to work with your original data without transforming it:

Formula:

Please Login or Register  to view this content.

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.