Forum Discussion
Solved
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 fu...
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
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.
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
- Thank you NikolinoDE that seems to work perfectly. You have just really made my day, I really appreciate the effort and response.
Cheers,