+ Reply to Thread
Results 1 to 4 of 4

Calculating consecutive working dates

  1. #1
    Registered User
    Join Date
    Hong Kong
    MS-Off Ver

    Calculating consecutive working dates

    Hi All,

    I have a difficult question on hand. Please see the attached excel example.

    In the attached excel, it shows 31 days in Jan 2020. The numbers (B2:AF4) represent the number of dutuies in each calender date.

    According to company rules:
    1.a person can have more than 1 duties on the same day
    2.a person cannot work for 7 consecutive days


    What excel function/VBA code can help to attain the below listed results? I willl use 1 person (Anna) to illustrate:

    - Check the values from 1 Jan to 7 Jan (B2:H2)
    - if there is one cell's value is not equal to to 0, then pop up message box "7 consecutive working days"
    - if is at lease one cell's value equal to 0, then continue to check the values from 2 Jan to 8 Jan (C2:I2)
    - The last checking process should end after checking the cell's value from 25 Jan to 31 Jan

    Thanks for your input
    Attached Files Attached Files
    Last edited by AliGW; 01-19-2020 at 03:23 AM. Reason: Irrelevant section of title removed.

  2. #2
    Forum Guru macropod's Avatar
    Join Date
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010

    Re: Request Solution: No 7 consecutive working dates

    Cross-posted at: https://www.mrexcel.com/board/thread...dates.1121117/
    Please read Excel Forum's Cross-Posting policy in rule 8: http://www.excelforum.com/forum-rule...rum-rules.html
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Registered User
    Join Date
    Hong Kong
    MS-Off Ver

    Re: Request Solution: No 7 consecutive working dates

    Hi Macropd,

    Readt the cross-posting rules and thanks.

    Yes, I have cross-posted my question to mrexcel.com in order to draw more insights. Both threads contain the same details.


  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)

    Re: Request Solution: No 7 consecutive working dates

    I've reinstated your request. There is no problem in having the same query on two forums just as long as you tell us that this is the case.

    If the problem has been solved on the other forum, then do us the courtesy of sharing the solution here and marking the thread as solved.

    Please do not remove posts in future. Thanks.

    There is no such Office version as 360 - please update your user profile to 365. Thanks.
    Last edited by AliGW; 01-19-2020 at 03:21 AM.

    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

+ 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. [SOLVED] Count instance of >8 days between consecutive dates in a list of dates
    By Keats713 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-07-2017, 11:18 AM
  2. Replies: 15
    Last Post: 08-06-2015, 04:14 PM
  3. [SOLVED] VBA add request code is not working
    By nmckever in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-18-2014, 12:21 PM
  4. [SOLVED] Concurrent Reps by Hour & Net New using VBA - Solution Modification Request
    By jai.anand@aol.in in forum Excel Programming / VBA / Macros
    Replies: 33
    Last Post: 03-11-2013, 09:37 AM
  5. Replies: 8
    Last Post: 12-13-2012, 01:15 PM
  6. [SOLVED] Request a Macro to Convert data into Dates (Multiple Dates Values separated by Line)
    By seenai in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-30-2012, 02:28 AM
  7. Replies: 3
    Last Post: 02-14-2012, 01:38 AM


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