+ Reply to Thread
Results 1 to 18 of 18

Finding Sequence Within Data Set

  1. #1
    Registered User
    Join Date
    12-18-2023
    Location
    Europe
    MS-Off Ver
    2021
    Posts
    12

    Finding Sequence Within Data Set

    Hi all,

    I have an excel (attached. I think) which has monthly data of employees workdays with the following columns:
    Name of an employee
    Date of month
    Day
    Activity (workday/Non-Workday)

    I need a formulae which will tell me if a person worked 6 days or more in one week (Sun-Sat).
    I need it to return to me True or False.

    thanks!
    Last edited by mmytb; 02-13-2024 at 04:13 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2403 (Windows 11 22H2 64-bit)
    Posts
    78,155

    Re: Finding Sequence Within Data Set

    I don't see where you have mocked up your expected results. What do you want to see? Where do you want to see it?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    12-18-2023
    Location
    Europe
    MS-Off Ver
    2021
    Posts
    12

    Re: Finding Sequence Within Data Set

    Apologies
    I would just expect to see it in a table format
    Ive corrected the excel and reuploaded
    i have no idea with how to go about it
    Last edited by mmytb; 02-13-2024 at 04:24 AM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2403 (Windows 11 22H2 64-bit)
    Posts
    78,155

    Re: Finding Sequence Within Data Set

    That's the same workbook with no changes. All I can see is one section highlighted in yellow in the source data - no expected results mocked up anywhere. Please try uploading the ammended workbook. Once we know what you are aiming for, we can help you to find a way to do it.

  5. #5
    Registered User
    Join Date
    12-18-2023
    Location
    Europe
    MS-Off Ver
    2021
    Posts
    12

    Re: Finding Sequence Within Data Set

    Apologies
    Now i've uploaded the correct file.
    I highlighted those cells since it should return True.
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2403 (Windows 11 22H2 64-bit)
    Posts
    78,155

    Re: Finding Sequence Within Data Set

    OK - right!

    Having a look.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2403 (Windows 11 22H2 64-bit)
    Posts
    78,155

    Re: Finding Sequence Within Data Set

    Does a week start on a Sunday or a Monday? From your highlighting, I am gueesing the former, but you need to confirm.

  8. #8
    Registered User
    Join Date
    12-18-2023
    Location
    Europe
    MS-Off Ver
    2021
    Posts
    12

    Re: Finding Sequence Within Data Set

    Weeks Starts on Sunday

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2403 (Windows 11 22H2 64-bit)
    Posts
    78,155

    Re: Finding Sequence Within Data Set

    Thanks. I shall have a look.

  10. #10
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,759

    Re: Finding Sequence Within Data Set

    Here is a power query way of determining how many days were worked in a particular week

    Please Login or Register  to view this content.
    Excel 2016 (Windows) 64 bit
    G
    H
    I
    9
    Name Week of Year Count
    10
    A
    31
    3
    11
    A
    32
    5
    12
    A
    33
    6
    13
    A
    34
    5
    14
    A
    35
    5
    15
    B
    31
    3
    16
    B
    32
    5
    17
    B
    33
    5
    18
    B
    34
    5
    19
    B
    35
    5
    20
    C
    31
    3
    21
    C
    32
    5
    22
    C
    33
    5
    23
    C
    34
    3
    24
    C
    35
    1
    Sheet: Current Month
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2403 (Windows 11 22H2 64-bit)
    Posts
    78,155

    Re: Finding Sequence Within Data Set

    Here's a 365 option:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2403 (Windows 11 22H2 64-bit)
    Posts
    78,155

    Re: Finding Sequence Within Data Set

    And here's a slightly better version:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2403 (Windows 11 22H2 64-bit)
    Posts
    78,155

    Re: Finding Sequence Within Data Set

    And all in one:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2403 (Windows 11 22H2 64-bit)
    Posts
    78,155

    Re: Finding Sequence Within Data Set

    Seen, but no reply ...

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

  15. #15
    Registered User
    Join Date
    12-18-2023
    Location
    Europe
    MS-Off Ver
    2021
    Posts
    12

    Re: Finding Sequence Within Data Set

    seen, but not yet had a chance to delve into the excel...

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2403 (Windows 11 22H2 64-bit)
    Posts
    78,155

    Re: Finding Sequence Within Data Set

    If it works, then the thread is solved, and should be marked as such. If you want an explanation of the formula, just ask.

  17. #17
    Registered User
    Join Date
    12-18-2023
    Location
    Europe
    MS-Off Ver
    2021
    Posts
    12

    Re: Finding Sequence Within Data Set

    Once i see that it works ill mark it as solved...
    thanks

  18. #18
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,313

    Re: Finding Sequence Within Data Set

    Here is another solution.

    The whole table in one go:

    Please try:
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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. Finding when sequence starts over
    By Bill1234 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-28-2022, 06:43 PM
  2. [SOLVED] Finding the next available lowest number in a sequence
    By cjcreamer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-20-2021, 03:03 PM
  3. finding number sequence in string
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 03-09-2015, 11:45 AM
  4. [SOLVED] Finding position of longest sequence
    By Marvo in forum Excel General
    Replies: 5
    Last Post: 03-09-2015, 09:50 AM
  5. Finding Missing Numbers In A Sequence
    By Kitty20 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 02-28-2015, 04:43 PM
  6. Finding max Value of an alpha numeic sequence
    By CROE in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-07-2012, 12:50 AM
  7. [SOLVED] Finding numbers missing from a sequence
    By andy in forum Excel General
    Replies: 4
    Last Post: 04-08-2005, 12:06 PM

Tags for this Thread

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