Extracting time in 60-minute increments

Assuming that the dates and times in B, C, and row 1 are stored as numbers and not text, then number formatting won't matter to the calculation scheme. I would expect to do something like.

1) Extract time from the Clear date time (column C), compare to the time in row 1 one column to the right (this probably means you will need a 24:00 entry in AC1), and take the smaller of the two MIN(MOD($C2,1),F$1)
2) Extract time from the call date time (column B), compare to the time in row 1 (same column), and take the larger of the two. MAX(MOD($B2,1),E$1).
3) Subtract 2 from 1 MIN(...)-MAX(...)
4) When an hour is not included in the times in B and C, then this difference will be less than 0. Force it to never return a value less than 0 =MAX(0,MIN(...)-MAX(...))

Note the mix of relative and absolute references and copy/paste/fill down and across.

Will that work for you?

I have tried AI and can't seem to get the right answer.

I every time I read this.

Originally Posted by MrShorty

Assuming that the dates and times in B, C, and row 1 are stored as numbers and not text, then number formatting won't matter to the calculation scheme. I would expect to do something like.

1) Extract time from the Clear date time (column C), compare to the time in row 1 one column to the right (this probably means you will need a 24:00 entry in AC1), and take the smaller of the two MIN(MOD($C2,1),F$1)
2) Extract time from the call date time (column B), compare to the time in row 1 (same column), and take the larger of the two. MAX(MOD($B2,1),E$1).
3) Subtract 2 from 1 MIN(...)-MAX(...)
4) When an hour is not included in the times in B and C, then this difference will be less than 0. Force it to never return a value less than 0 =MAX(0,MIN(...)-MAX(...))

Note the mix of relative and absolute references and copy/paste/fill down and across.

Will that work for you?

Thanks MrShorty. Based on your instructions, I am using the following formula in E2 and subsequent columns, relative and absolute as applicable, but am getting zeros.

=MAX(0,((MIN(MOD($C2,1),E$1))-MAX(MOD($B2,1),E$1)))

Attachment 849906Screenshot 2023-11-17 164605.png

You missed the reference to column F in the MIN(...) portion of the formula: =MAX(0,((MIN(MOD($C2,1),F$1))-MAX(MOD($B2,1),E$1)))

It is difficult to debug a picture.

I notice that your formula cells are all displaying 0 and not 0:00(:00). What number format have you applied to those cells? Any value 1 hour or less will display as 0 when formatted as "number with 0 decimal places." Formatted as general, there would be a value between 0 and 1/24 (0.04166...) in the cell. Make sure you are applying a suitable time (elapsed hour) format to these cells.

The other thing that might go wrong is that the values in row 1 are text strings rather than numbers. Look at row 1 and make sure those are numbers and not text strings (=ISTEXT(E1) could be useful for this).

Here works the formula of MrShorty perfect. See attachment: