+ Reply to Thread
Results 1 to 8 of 8

Count dates last week, Mon-Fri

  1. #1
    Registered User
    Join Date
    07-24-2018
    Location
    United States
    MS-Off Ver
    MS2010
    Posts
    4

    Count dates last week, Mon-Fri

    Hello, I am in need of a formula that counts dates that fall last week, Monday through Friday. The week will always change (e.g., every Monday when I pull my data, I need to count dates from the week prior). Is this possible?

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Count dates last week, Mon-Fri

    Yes. Assuming that you have today's date stored in A1 (i.e. =Today())

    Then formula for calculating last Monday.
    =A1-WEEKDAY(A1,3)-7

    So formula would be something like..
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    07-24-2018
    Location
    United States
    MS-Off Ver
    MS2010
    Posts
    4

    Re: Count dates last week, Mon-Fri

    Thank you. So with this formula, I would need to enter in my date range each week?

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Count dates last week, Mon-Fri

    Without knowing how your data is structured. Can't really comment on that.

    I'd recommend uploading sample workbook of how your data is set up, with desensitized data and how/where you want your result to be shown.

    To attach a workbook, use "Go Advanced" button found at bottom right of Quick Reply/Edit menu. Then find "Manage attachments" hyperlink and click on it. It will launch new window/tab where you can upload files.

  5. #5
    Registered User
    Join Date
    07-24-2018
    Location
    United States
    MS-Off Ver
    MS2010
    Posts
    4

    Re: Count dates last week, Mon-Fri

    I have attached the "Desen Report." I am trying to have the weekly submittals on the "RDash" tab to count from the "CanData" tab. Essentially, I will be importing the data into "CanData" tab each Monday and am looking for a formula to count the "RRtoHM Completed Date" (Col L). Thank you for all your help with this!
    Attached Files Attached Files

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Count dates last week, Mon-Fri

    Something like below then.

    If it is between Jul 9 to Jul 13.
    In B1:
    =DATE(2018,7,16)-WEEKDAY(DATE(2018,7,16),3)-7

    Note: For your actual use replace Date() with Today(). Today() function will update to current date.
    And formula used will automatically obtain date of previous week's Monday.

    Then in Weekly Submittals:
    =COUNTIFS(CanData!$J:$J,">="&$B$1,CanData!$J:$J,"<="&($B$1+4),CanData!$B:$B, C3)

    Copy to all applicable cells. See attached.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-24-2018
    Location
    United States
    MS-Off Ver
    MS2010
    Posts
    4

    Re: Count dates last week, Mon-Fri

    Thank you so much! This will make life a lot easier!

  8. #8
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Count dates last week, Mon-Fri

    You are welcome

    If this resolves your issue, please mark the thread as solved by using Thread tool found at top of your initial post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Count days of week based on dates and specific text
    By elv28 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-20-2018, 01:23 AM
  2. Count workdays between 2 dates per week
    By Framboosje in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-01-2018, 01:35 PM
  3. [SOLVED] Count Week numbers given start and finish week number
    By Vassen in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-30-2016, 01:59 AM
  4. Replies: 5
    Last Post: 03-27-2015, 08:07 AM
  5. Pivot Table - 2 columns of dates, count per week
    By ashakespeare in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 09-27-2012, 07:04 AM
  6. need to convert list of dates to count no. of dates by week
    By neowok in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 01-30-2006, 11:54 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1