Steven1985
Board Regular
- Joined
- Mar 2, 2011
- Messages
- 66
Hi,
Key – A3 = Date Received
AO3 - Date Completed
Controls A10:A47 is all the non working days including bank holidays
I have a current formula (Below) that count the date and time in HH:MM from one cell (A3) to (AO3).
This has included NETWORKDAYS to exclude counting if a weekend of bank holiday. This data is in Controls A10:A47
=IF(AO3="","",NETWORKDAYS(A3,AO3,Controls!A$10:A$47)-1-(NETWORKDAYS(A3,A3,Controls!A$10:A$47)*MOD(A3,1)>MOD(AO3,1))&" days "&TEXT(AO3-NETWORKDAYS(A3,A3,Controls!A$10:A$47)*A3,"h:mm"))<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
I would ideally like to count business hours Only so if it came it at 11pm Tuesday and the Outcome was 10am Wednesday id like to read 1 hour.
There 8 hours in the working day 9 - 5 so if it came it at 9am Tuesday and didn’t have the outcome until Wed 10am Id like it to say 1 day 1 hour.<o></o>
Can anyone help please?
Many Thanks
Key – A3 = Date Received
AO3 - Date Completed
Controls A10:A47 is all the non working days including bank holidays
I have a current formula (Below) that count the date and time in HH:MM from one cell (A3) to (AO3).
This has included NETWORKDAYS to exclude counting if a weekend of bank holiday. This data is in Controls A10:A47
=IF(AO3="","",NETWORKDAYS(A3,AO3,Controls!A$10:A$47)-1-(NETWORKDAYS(A3,A3,Controls!A$10:A$47)*MOD(A3,1)>MOD(AO3,1))&" days "&TEXT(AO3-NETWORKDAYS(A3,A3,Controls!A$10:A$47)*A3,"h:mm"))<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o>I would ideally like to count business hours Only so if it came it at 11pm Tuesday and the Outcome was 10am Wednesday id like to read 1 hour.
There 8 hours in the working day 9 - 5 so if it came it at 9am Tuesday and didn’t have the outcome until Wed 10am Id like it to say 1 day 1 hour.<o
></o>Can anyone help please?
Many Thanks
