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

It is cell W3 specifically FYI.

It asks for a password due a missing tab but this can be ignored i think

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

If you replace the red part with the blue one below it will return correct data
=IFERROR(IF(ISNUMBER(O3),NETWORKDAYS(E3,O3,BankHols18)-(IF(K3<1,K3,0))-(IF(IFERROR((T3/8),""),U3,0))),"")

Thanks Paul -

It still returns 3.5 Days? It should return 4.5 Days as follows:

Network Days Between Mon. 4th June and Fri. 8th June = 5 WORKING DAYS

MONDAY 4th JUNE
4 H ABSENCE = 0.5 of Working Day (as shown in Col. K) (8 hours = full working day), so it needs to discount 4 hours (0.5 Day) from the 5 working days = 4.5 Days.

Hope that makes sense!

Thanks,

Stuart

Thanks for the revised code, I'll give it a whirl shortly

Thanks,

Stuart