+ Reply to Thread
Results 1 to 13 of 13

Rota in Excel - Formula for creating daily overview based on shifts times

  1. #1
    Registered User
    Join Date
    04-11-2019
    Location
    Birmingham, England
    MS-Off Ver
    2010
    Posts
    4

    Rota in Excel - Formula for creating daily overview based on shifts times

    Hi All

    I am a new member of the Forum and really hope someone can help me out.

    My company currently use a rota that is created and maintained in excel. Horizontally across the top we have the names of each member of staff (roughly 80) and vertically we have the day of the week and the date (in separate columns). Along the day and date line will be the shift that person is working each day, for example 6-2 or 2-10 etc. We have numerous shift times throughout the day.

    What we currently do as a manual process is type up who is working what shift on each day and we use this as a daily resource plan to issue breaks etc. What I am looking to do is create a new tab where I can type the date in and it will pull across the names of the people who are working 6-2 / 2-10 that day and so on.

    Can this be done with formula?

    TIA

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

    Re: Rota in Excel - Formula for creating daily overview based on shifts times

    Welcome to the forum!

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    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. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    04-11-2019
    Location
    Birmingham, England
    MS-Off Ver
    2010
    Posts
    4

    Re: Rota in Excel - Formula for creating daily overview based on shifts times

    Hi Ali

    Many thanks for the prompt response.

    I have slimmed down the attached rota to show a 2 week block on the first tab with 20 staff on it. On the second tab is the format I would like the daily sheet to appear, columns are headed with the shift time and then the name of that person/s working that particular shift. I am only looking for those particular shift times as headed on tab 2, other information you will see such as "OFF", "H" or "R#" can be ignored.

    As requested on tab 2 I have created a mock up for 06/05/19.

    I look forward to response, and thanks again for your help.

    Jamie (pokerjam)
    Attached Files Attached Files

  4. #4
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,410

    Re: Rota in Excel - Formula for creating daily overview based on shifts times

    Try the attached.
    There is a combobox on "Daily" that is populated from the dates on "Rota"
    Right click "Daily" tab on small menu select "View Code" (this open the code window of sheet).
    The code is almost self explanatory and expandandable by adjusting parameters to suit.
    There is no sheet formula to get corrupted.
    torachan.
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,371

    Re: Rota in Excel - Formula for creating daily overview based on shifts times

    The OP specifically asked for a formula solution, so VBA might not be appropriate (possibly a workplace restriction):

    Can this be done with formula?

  6. #6
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,410

    Re: Rota in Excel - Formula for creating daily overview based on shifts times

    If I was cleverer enough I would have offered a 'sheet' solution.
    Sometimes cluttering a sheet with formula is counterproductive.
    VBA was invented for a purpose, use it.
    Alternatively waste hours looking for the 'Holy Grail'.
    Just trying to be helpful, in future I will read and analyse ever post to the 'n'th degree before replying,
    torachan.

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

    Re: Rota in Excel - Formula for creating daily overview based on shifts times

    Not saying that you shouldn't have suggested it, just warning you that it might not be what is required.

  8. #8
    Registered User
    Join Date
    04-11-2019
    Location
    Birmingham, England
    MS-Off Ver
    2010
    Posts
    4

    Re: Rota in Excel - Formula for creating daily overview based on shifts times

    Hi Guys

    Torachan - Thanks for the reply and looking at my Rota - I didn't ask for VBA simply because I have little to no knowledge of VBA and my concern was that if something doesn't work that I can't fix it, whereas with Formula once I can see for myself how it works I tend to get my head around it and can add or amend where necessary.

    Ali - As mentioned above Formula is certainly my preferred option here although we don't have VBA restrictions at work it is probably a little complex for me.

    Jamie

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

    Re: Rota in Excel - Formula for creating daily overview based on shifts times

    In B2:

    =IFERROR(INDEX('CC rota'!$A$1:$X$1,SMALL(IF('CC rota'!$A$2:$A$16=Daily!$A$1,IF('CC rota'!$E$2:$X$16=Daily!B$1,COLUMN('CC rota'!$E$1:$X$1))),ROWS($A$1:A1))),"")

    ... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Once confirmed, copy across and down.

  10. #10
    Registered User
    Join Date
    04-11-2019
    Location
    Birmingham, England
    MS-Off Ver
    2010
    Posts
    4

    Re: Rota in Excel - Formula for creating daily overview based on shifts times

    Hi Ali

    I have tried this but the formula is just entering as text not with the braces you specified. I've even applied this to my master document and changed the cell references but still no joy

    I appreciate your patience with this!

    Jamie

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

    Re: Rota in Excel - Formula for creating daily overview based on shifts times

    Yes, I noticed that myself!

    Do this:

    1. Remove formulas from column B.
    2. Select column B and set the format to general on the Home ribbon.
    3. Now paste the formula into the cell and do as instructed in my original message.
    Attached Files Attached Files
    Last edited by AliGW; 04-15-2019 at 08:08 AM.

  12. #12
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,410

    Re: Rota in Excel - Formula for creating daily overview based on shifts times

    Hi Pokerman.
    Just a small encouragement, expand your horizon, experiment with VBA in your own time.
    My personal reasons for using it in preference to on sheet formula are based upon my own experiences.
    When working in a multi-person environment there is always the need to stop meddling little fingers modifying your sheets.
    To this end I find the VBA approach far more flexible with regard to security.
    Look at the attached code is it REALLY anymore difficult to figure out than complex sheet formula ???
    As a 76 year old with the attention span of a goldfish I know which I find easier.

    Ali, To use B1:M1 as a 24 hour format I found I had to format it as 'Text' as 'General' it got confused with dates.

    Capture.JPG

    Rota Example(tora).pdf
    Last edited by torachan; 04-16-2019 at 01:55 AM. Reason: pdf did not load on original post

  13. #13
    Registered User
    Join Date
    02-06-2021
    Location
    London
    MS-Off Ver
    16.16.27 (201012)
    Posts
    1

    Re: Rota in Excel - Formula for creating daily overview based on shifts times

    Really helpful advice, thank you - but still stuck!
    Have entered formula but not recognising values
    Using Excel for mac
    Ideas on 'activating array' do not get me curly brackets, just visible formula in cell vs value

    Any ideas what Ive done wrong?? (attached rota)
    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. Excel for shifts of areas and times
    By barbara70 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-10-2016, 01:54 AM
  2. daily overview of time records
    By CCFF in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-15-2016, 09:05 AM
  3. Replies: 0
    Last Post: 04-07-2015, 11:39 PM
  4. Replies: 0
    Last Post: 03-02-2015, 07:59 AM
  5. Rota with 4 shifts - calculate hours?
    By graboid in forum Excel General
    Replies: 3
    Last Post: 11-01-2014, 03:40 PM
  6. [SOLVED] Some shifts wont add on my staff rota weekly total
    By bigtiger1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-11-2013, 09:53 AM
  7. creating a staff rota for 24 hour shifts
    By RONJONES in forum Excel General
    Replies: 4
    Last Post: 09-06-2013, 12:02 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