NETWORK DATES, Dates and Times Working In Unison...

Hi All -

I am currently trying to work a formula that will total NETWORK DAYS between 2 dates, but also discount hours off either of the two 'bookend' dates if it is a half-day / quarter-day or whatever (this ties in with HR payroll, hence why it has to be specific. I just can't get the formula right to correctly total 2 full days, a portion of a day and a full day (or a full day and the latter bookend be a portion of a day) or both bookend days be portions of a day.

My formula is

=IF(ISNUMBER(O3),NETWORKDAYS(E3,O3,BankHols18)-(IF(K3<=1,K3,0))-(IF(U3<=1,U3,0)),K3)

i.e. if a second date is given in O3, (therefore a 'range' / bookends, rather than a single day), calc. the network days between start and finish dates, BUT if the first or last day are less than 1.00 of a day (decimals and time play into this so a bit of a pain!!) discount this off the total amount. It has to allow for either the first or last day not being a full day. Sometimes I can get it work with one but not the other!!!

Any help appreciated. I have tried both 'IF' and 'SUMIF' but not sure which is better for this?

I have attached the sheet. I have working on it a while so maybe I'm missing something obvious!!

Thanks,

Stuart