Correct / Modify Formula for NETWORKDAYS

[shinobi11]

Hi I have an existing tracker that follows our local time but since we work graveyard the NETWORKDAYS formula considers Sat-Sun as rest days. However when our shift goes beyond Saturday morning 12:01 AM it gives a negative value but technically it is still our Friday shift.

I would like to know if theres a way or If Condition or maybe another hidden column that will correct this in order to still consider working days for Saturday morning from 12:00AM to 7:00AM?

Attached is a sample tracker I am currently using,

[kvsrinivasamurthy]

Try this in AJ2 then copy down.

=IF(OR(I2="",Y2=""),"",(NETWORKDAYS.INTL(I2,Y2,1)-Z2)-MOD(I2,1)-(1-MOD(Y2,1))+(WEEKDAY(Y2,1)=7)*MIN(MOD(Y2,1),"7:00"))

[shinobi11]

Thank you for your reply, can you please explain how this formula works? I mean what is the interpretation of the additional formula?

Thanks in advance for your help I appreciate it.

[kvsrinivasamurthy]

(WEEKDAY(Y2,1)=7)
Checks for weekday as Saturday .
*MIN(MOD(Y2,1),"7:00")
If so the minimum of Worked hours OR 7 hours will be added.