Workdays for Current Work Week

Posted on 2014-08-25
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:


Any ideas?
Question by:Lawrence Salvucci
    LVL 27

    Expert Comment

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

    LVL 27

    Accepted Solution

    I've attached an example workbook that calculates the used workdays with the above logic like so:

    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.

    LVL 1

    Author Comment

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

    Expert Comment

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

    LVL 1

    Author Closing Comment

    by:Lawrence Salvucci
    Thank you very much! Didn't see your second post right away but yes this is exactly what I was looking for. Thank you!
    LVL 27

    Expert Comment

    by:Glenn Ray
    You're welcome.


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    VBA to delete range of cells in row NOT entire row 11 36
    Search multiple lines 3 27
    Check version 13 45
    Copy a row 12 31
    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now