I have one range of cells showing a time range, and another showing sunrise and sunset times (the sunrise/set times are not exact, I am just approximating for this example). What formula could show the duration of that time range which is at night vs during the day? I have input these values manually as an example of what I am looking for.

I am using Dates in the In/Out columns and Times in the Sunrise/set columns. I will likely need these to be the same types of values, I’m not sure which will be easier for the calculation when dealing with 24 hour periods spanning midnight (likely dates).

Output as either HH:MM or decimal hours (as shown) would work.

Screenshot of table

I have tried using duration values for both but am running into troubles calculating the overlap.

Times are decimal values between 0 and 1 so I have tried the sum of what part of each range overlaps.