+ Reply to Thread
Results 1 to 6 of 6

Count days of week based on dates and specific text

  1. #1
    Registered User
    Join Date
    06-20-2018
    Location
    Canberra
    MS-Off Ver
    2016
    Posts
    3

    Count days of week based on dates and specific text

    Hi all,

    I'm not sure if this is something that Excel will be able to help me with, but thought I would post and ask if anyone has any ideas.
    I work for an organisation where we rent out rooms for people to stay in. There are different charges depending on the day of the week (weekday, Saturday, Sunday, Public Holiday) and people set up a certain number of these types of night to begin with for, say, 12 months. We need to know when taking a booking how many of each night type they have left.
    What I want is to integrate a counting mechanism with our booking system, which looks something like this:

    25/06/2018 26/06/2018 27/06/2018 28/06/2018 29/06/2018 30/06/2018 1/07/2018
    Monday Tuesday Wednesday Thursday Friday Saturday Sunday

    Bedroom 1 Jane Jane Jane Jane Jane Jane Jane
    Bedroom 2 Tom Tom Tom Bob Bob Bob Bob
    Bedroom 3 Alice Alice Alice Alice


    And I want Excel to count and collate the various day types for each person.

    Total Weekdays Weekdays Used Total Saturdays Saturdays Used Total Sundays Sundays Used Total Public Holidays Public Holidays Used
    Jane 20 5 10 1 10 1 2 0
    Tom 10 3 10 0 10 0 5 0
    Bob 35 2 5 1 5 1 1 0
    Alice 20 2 2 1 2 1 1 0

    I am happy to have multiple sheets (eg. if I need a list of Public Holiday dates), but it probably wouldn't be viable to have a sheet per user as it we have 100+ clients and always having more.


    Any assistance would be much appreciated!

    Thanks,
    Erin

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Count days of week based on dates and specific text

    You would be better attaching a sample workbook showing sample data and expected output.

    Go Advanced -> Manage Attachments -> Upload

  3. #3
    Registered User
    Join Date
    06-20-2018
    Location
    Canberra
    MS-Off Ver
    2016
    Posts
    3

    Re: Count days of week based on dates and specific text

    Sorry, the formatting didn't work the way I thought it would. Attached is a sample workbook with this data.

    Thanks
    Erin
    Attached Files Attached Files

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

    Re: Count days of week based on dates and specific text

    It would be easier if you collected and collated the booking data in a normalised format (i.e. one row per booking) and then generated both 'user views' from that data.

    Normalised data would look like this:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    1
    Date Day Room Client
    2
    25/06/2018
    Monday Bedroom 1 Jane
    3
    25/06/2018
    Monday Bedroom 2 Tom
    4
    26/06/2018
    Tuesday Bedroom 1 Jane
    5
    26/06/2018
    Tuesday Bedroom 2 Tom
    6
    27/06/2018
    Wednesday Bedroom 1 Jane
    7
    27/06/2018
    Wednesday Bedroom 2 Tom
    8
    28/06/2018
    Thursday Bedroom 1 Jane
    9
    28/06/2018
    Thursday Bedroom 2 Bob
    10
    28/06/2018
    Thursday Bedroom 3 Alice
    11
    29/06/2018
    Friday Bedroom 1 Jane
    12
    29/06/2018
    Friday Bedroom 2 Bob
    13
    29/06/2018
    Friday Bedroom 3 Alice
    14
    30/06/2018
    Saturday Bedroom 1 Jane
    15
    30/06/2018
    Saturday Bedroom 2 Bob
    16
    30/06/2018
    Saturday Bedroom 3 Alice
    17
    01/07/2018
    Sunday Bedroom 1 Jane
    18
    01/07/2018
    Sunday Bedroom 2 Bob
    19
    01/07/2018
    Sunday Bedroom 3 Alice
    Sheet: Sheet3

    Then it's just a case of using the COUNTIFS function to extract the information you need.
    Last edited by AliGW; 06-20-2018 at 01:17 AM.
    Ali


    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.

  5. #5
    Registered User
    Join Date
    06-20-2018
    Location
    Canberra
    MS-Off Ver
    2016
    Posts
    3

    Re: Count days of week based on dates and specific text

    Hi,

    Thanks for your reply. I'm a little confused about how I would be able to generate this data. Are you able to provide me with an example or a function?

    Thanks
    Erin

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,108

    Re: Count days of week based on dates and specific text

    I think you may have misunderstood.

    You are making the mistake many do when starting out with Excel: in your file, you are gathering together your data in a format that is easy to read and interpret. That's not the optimum way to do it.

    You should start by collecting the data in the format I have shown you - that's your raw data, what is input manually by your data monkey. From that data, you generate the various user-friendly views and analyses of that data that you wish to have. Does this make any more sense?

    I don't have time to show you how to build the two tables in your attachment at the moment as I am just about to go out to work, but I am sure someone else here will run with it.

    If you already have a lot of data, it can easily be transformed into what you see above using Power Query, which is what I used on your data. Once transformed, you would add the data to your database using this collection table going forward.
    Last edited by AliGW; 06-20-2018 at 01:27 AM.

+ 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. Late count based on number of days between two dates for a list of dates
    By Keats713 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-03-2017, 11:03 PM
  2. Late count based on number of days between two dates for a list of dates
    By Keats713 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-03-2017, 06:35 PM
  3. [SOLVED] Rounding dates to specific working days of the week
    By Sarah C in forum Excel General
    Replies: 7
    Last Post: 07-13-2016, 12:19 PM
  4. Incrementing dates in yymmdd format based on days of week
    By peakoverload in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-25-2016, 07:18 AM
  5. Plotting chart based on dates but extracting days of week only
    By pelachrum in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 05-21-2013, 11:03 AM
  6. [SOLVED] Calculating a week of dates/days based on the beginning date entered...
    By tiffany04530 in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 11-27-2012, 09:55 AM
  7. Determine future dates based on selected days of the week
    By hnowack in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-26-2008, 09:51 PM

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