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
6
Medium Priority
?
216 Views
Last Modified: 2014-08-25
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
Comment
Question by:Lawrence Salvucci
  • 4
  • 2
6 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
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

by:
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

by:Lawrence Salvucci
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:Glenn Ray
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

by:Lawrence Salvucci
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

by:Glenn Ray
ID: 40283547
You're welcome.

-Glenn
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question