business days between two dates
2008
Excel: A formula to count the number of weekdays, hours and minutes between two dates and times?
Here’s the scenario:
I’m trying to create a formula in Excel (Excel 2003) that will calculate the difference of business weekdays(Monday-Friday), hours and minutes between two dates & times.
Example:
Cell (A1) is 01/01/10 12:00 AM
Cell (B1) is 01/05/10 05:30 AM
The result needs to be displayed in Cell (C1) and should read:
“2 days, 5 hours, 30 minutes” or “2 days, 5:30″ or “02:05:30″ (omitting 01/02/10 and 01/03/10 since they are business weekends). I can clarify further if necessary. Thanks for the help!
2 Days 05:30
=TEXT(NETWORKDAYS(A1,B1) -1 + (MOD(B1,1)-MOD(A1,1)), “d “”days, “”hh:mm”)
2 Days, 5 hours, 30 minutes
=TEXT(NETWORKDAYS(A1,B1) -1 + (MOD(B1,1)-MOD(A1,1)), “d “”days,”" [h] “”hours,”" m “”minutes”"”)
Or use this and format the cell with any custom Day-Time format you want.
=NETWORKDAYS(A1,B1) -1 + (MOD(B1,1)-MOD(A1,1))
NETWORKDAYS is part of the Analysis Toolpak
http://office.microsoft.com/en-us/excel/hp011277241033.aspx
The NETWORKDAYS Function
http://office.microsoft.com/en-us/excel/HP052091901033.aspx
Comment