Forum Discussion
Shift end time and overnight rest break
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.
- Ok here you go then, hope this works
https://1drv.ms/x/c/b1cf978db6df0205/EaF6o3PgCnFLneqe21A40GIB9aGkDXR5N1LfmgzAG-5h6Q?e=KuTN0R
Should also mention sometimes the overnight break will not be entered so using B?-1 is not a viable option.
Thank you