I have an activity logger and cells on “Day 1” A16 to B33 are start and end times. A16 is populated from an entry in C10 and G10 is populated from the last time entered in B16:33. The array may be fully or partially populated depending on number of activities on the day. All cells are formatted as 12 hour time and no date is used. C16 to 33 use drop down lists for activity.

The issue arises when an overnight rest break is entered in B? and G10 displays the time of the following morning.

What I want is to have G10 ignore the following morning if the previous time is more than 9 hours behind (only entry for that length of time will be an overnight break). It may prove difficult to use PM and AM as some shifts work across midnight but if that is easiest then that can be arranged.

To this point AI has not been able to achieve a result but I will keep trying.

Edit: Continued searching has found this thread https://techcommunity.microsoft.com/t5/excel/check-if-a-time-falls-between-a-time-range-crossing-date-lines/m-p/3843408#M194413 andit seems this function 

=IF( MEDIAN($D$2,$D$2+MOD($D$3-$D$2,1),$C4) = $C4, ” O”, “” )

or a variant may prove useful

Any help would be appreciated

Thank you

​I have an activity logger and cells on “Day 1″ A16 to B33 are start and end times. A16 is populated from an entry in C10 and G10 is populated from the last time entered in B16:33. The array may be fully or partially populated depending on number of activities on the day. All cells are formatted as 12 hour time and no date is used. C16 to 33 use drop down lists for activity. The issue arises when an overnight rest break is entered in B? and G10 displays the time of the following morning. What I want is to have G10 ignore the following morning if the previous time is more than 9 hours behind (only entry for that length of time will be an overnight break). It may prove difficult to use PM and AM as some shifts work across midnight but if that is easiest then that can be arranged. To this point AI has not been able to achieve a result but I will keep trying. Edit: Continued searching has found this thread https://techcommunity.microsoft.com/t5/excel/check-if-a-time-falls-between-a-time-range-crossing-date-lines/m-p/3843408#M194413 andit seems this function =IF( MEDIAN($D$2,$D$2+MOD($D$3-$D$2,1),$C4) = $C4, ” O”, “” )or a variant may prove useful Any help would be appreciatedThank you  Read More

​