Workdays for Current Work Week

I'm trying to get the # of workdays used for the current week. I have the starting date and ending date of the weeks in columns S & T. I need to show the # of workdays (not including today) used in column U. This is the formula I'm using to get the # of workdays used for the current quarter. How can I do this for the current week? And since I have all the weeks listed in my spreadsheet I would need to show all prior weeks as having used all the days for that week. I have a list of Holidays since I use it in my other formula. Here is the formula I'm using for the # of workdays used in the quarter:

NETWORKDAYS(EOMONTH($E$1,MOD(-MONTH($E$1),3)-3)+1,$E$1,Holidays)

Any ideas?
LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
Just to clarify, by example:

For this calendar week, starting on Sunday, 8/24/2014, you'd want to see the following "days used" values:
Current Date - Day - Days Used
8/24/2014 - Sun - 0
8/25/2014 - Mon - 0
8/26/2014 - Tue - 1
8/27/2014 - Wed - 2
8/28/2014 - Thu - 3
8/29/2014 - Fri - 4
8/30/2014 - Sat - 5

-Glenn
0
Glenn RayExcel VBA DeveloperCommented:
I've attached an example workbook that calculates the used workdays with the above logic like so:
=IF(TODAY()>T2,NETWORKDAYS(S2,T2,Holidays),IF(TODAY()<S2,0,NETWORKDAYS(S2,TODAY(),Holidays)-IF(TODAY()<T2,1,0)))

If you actually have a cell value with the current date in it, you can replace all occurrences of TODAY() with the location of that cell (either a range name or absolute cell reference).

I've attached a workbook that shows both methods.

Regards,
-Glenn
EE-WeeklyWorkdays.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
No, like this:

Column S           Column T          Column U

08/24/14            08/30/14            1 - If today was Tuesday 8/26/14 then there would be 1 day used this week so far
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Glenn RayExcel VBA DeveloperCommented:
Sorry for the confusion; I was trying to determine the correct logic for calculating days, not show actual layout.

Check my previously-submitted workbook; I think it shows the result you've demonstrated above.

-Glenn
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thank you very much! Didn't see your second post right away but yes this is exactly what I was looking for. Thank you!
0
Glenn RayExcel VBA DeveloperCommented:
You're welcome.

-Glenn
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.