Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Workdays for Current Work Week

Posted on 2014-08-25
Medium Priority
216 Views
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?
0
Question by:Lawrence Salvucci
• 4
• 2

LVL 27

Expert Comment

ID: 40283490
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

LVL 27

Accepted Solution

Glenn Ray earned 2000 total points
ID: 40283520
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

LVL 1

Author Comment

ID: 40283522
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

LVL 27

Expert Comment

ID: 40283529
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

LVL 1

Author Closing Comment

ID: 40283533
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

LVL 27

Expert Comment

ID: 40283547
You're welcome.

-Glenn
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
###### Suggested Courses
Course of the Month15 days, 17 hours left to enroll