Count Between dates using NETWORKDAYS

[Steven1985]

Hi,

I have an exisiting formula to count number of days and hours between 2 date ranges-

=IF(AP3="","",AP3-A3)

but i need the dates only for working days. i have made a list of non working days on a 'CONTROL' tab Column A10-A47.

aNY IDEA HOW i CAN INCLUDE THIS?

Thanks

 

[Expiry]

This should work. You don't have to list weekends within your non-working days, just days such as bank holidays or holidays, etc.

=NETWORKDAYS(A3,AP3,A10:A47)

 

[Steven1985]

This is the formula im using below but comes back with 'FALSE'

=IF(AO3="","",AO3-A3)=NETWORKDAYS(A3,AO3,Controls!$A$10:$A$47)

Just using the )=NETWORKDAYS(A3,AO3,Controls!$A$10:$A$47) doent give the information I need as I need to count the data and time from column A and include a date and time in column A0 for then column AP (where the formulas held to count d hh:mm).

 

[Misca]

NETWORKDAYS only returns the number of days:

=IF(AP3="","",NETWORKDAYS(A3,AP3,CONTROL!$A$10:$A$47))

Since you want to get the hours as well, you need to work those out using some other formula.

The easiest way might be using the NETWORKDAYS formula for the whole days only (= A3+1,AP3-1) and multiply that with the number or hours per day + calculate the first & last days as hours only: First day = hours from starting point till midnight & last day = from midnight to the ending point. If you're only calculating working hours adjust the starting & ending hours from midnight to the correct time.

 

[Expiry]

you could revert to your original formula that gave you the days and time, and subtract 24 hours for any day in your list of non working days, using countif.