Forum Discussion
Shift end time and overnight rest break
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
Formula for G10:
=LOOKUP(2,1/(B16:B32<>"")/(MOD(B16:B32 - A16:A32, 1) <= TIME(9, 0, 0)), B16:B32)
Maybe it will work now
8 Replies
Here is a simple example (in the attached file) as described in one of your comments.
I'm not sure if this is what you imagined, but maybe it will help you further in your project.
- Thanks NikolinoDE but when you enter times it still shows the overnight time.
Enter 8:00 into C10 then 11:00 into B16
G10 shows 11:00
Enter 14:00 into B17
G10 shows 14:00
Enter 6:30 into B18 (this is the overnight break which I want to lose and this could occur anywhere in the range B16, usually from abut B18 or 19 at the earliest, to B32)
G10 shows 6:30
At this point I want G10 to show 14:00 as that is the last entry for today.Formula for G10:
=LOOKUP(2,1/(B16:B32<>"")/(MOD(B16:B32 - A16:A32, 1) <= TIME(9, 0, 0)), B16:B32)
Maybe it will work now
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
HansVogelaar can you please explain to me how to attach an excel file? I cannot work out how to do it, it says file type is not supported when I drop it in.
Edit: In the meantime, cell C10 is first manual entry point.
A16 is =IF(C10="", "", C10) and B16 is manual entry
A17 is =IF(B16="", "", B16) and B17 is manual entry
continue to A32 which is
=IF(B31="", "", B31) and B32 is manual entry. Original post had error, row 32 is the last not 33 as stated.
G10 uses =LOOKUP(2,1/(B16:B32<>""),B16:B32) to display last entry.
If you wish, upload the workbook to for example Google Drive, OneDrive or Dropbox.
Then obtain a share link to the uploaded file, and paste that link into a reply.
