Something like the attached?
I changed the value of E9 to show an example of overnight hours.
This being said, your extensive use of merged cells will get you in trouble when you will want to analyze further data. Merged cells should be avoided at all costs.




Also, if I understand part of your problem, when summing a range, XL does not care about text

Attached Files

Template.xlsx (29.3 KB, 21 views)

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

@Pecoflyer

I very much appreciate your help. Great solution!

If it's not too much trouble, would you kindly explain the formula to me?
I know why you used ISNUMBER and the ranges, of course, but not sure of the rest.

Thank you again for your kindness.

Karen