+ Reply to Thread
Results 1 to 25 of 25

HELP needed for staff rota

  1. #1
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    184

    HELP needed for staff rota

    New to forum so Hi all,

    I have an ongoing project where I fill in cells of scheduled work times for staff that results in displaying their start and finish times, sometimes over 7 hours and always 8 hours or more this will include a scheduled unpaid break so this time needs to be deducted. My current formula does all of this but sometimes when deducting the break the finish time is incorrectly adjusted. For example, staff member works 8 hours and takes half hour break, 8am-4pm, this displays correctly. Using same formula, they work 7 hours 8am-3pm with break and finish time shows as 2:30pm.

    Current setup is

    Columns A=Staff name, B=7am-8am, C=8am-9am, D=9am-10am........I=2pm-3pm, J=Start time, K=Finish time, L=Lunch time, M=Total worked
    Rows 1 Labels, i.e. B1=7am-8am, L1=Lunch time
    Row 2=Staff name 1
    Row 3=Staff name 2
    Row 4=Staff name 3
    etc

    Formula in column J is
    =IFERROR(TEXT(DOLLARFR(MATCH(TRUE,INDEX($B2:$I2<>"",,),0)+LEFT($B$1,FIND("-",$B$1)-1)-INDEX($B2:$I2,MATCH(TRUE,INDEX($B2:$I2<>"",,),0)),60),"00.00"),"")

    Formula in column K is
    =IFERROR(TEXT(DOLLARFR($M2+($M2>=7)*0.5+DOLLARDE(V3,60),60),"00.00"),"")

    Column L, I still need the formula for this. Using LE and LL (Lunch Early and Lunch Late), LE will be the first half hour of the hour i.e. 12:00 and LL last half hour 12:30

    Formula in column M is
    =COUNTA(C3:T3)-(SUM(COUNTIF(C3:T3,{"LE","LL"})/2))

    Entering number 1 in to cells C2-I2 except for G2 which would have LE, the total hours displays correctly showing 6.5. But the finish time is 14:30 and this should be 15:00 with a break at 12:00 making total hours paid 6.5

    Im hoping someone can help me fix these time issues and maybe a formula for column L?

    Thanks in advance.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: HELP needed for staff rota

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    184

    Re: HELP needed for staff rota

    I have uploaded a sample sheet (I think lol)

    Table 1 shows the working formulas giving the incorrect results

    What I am looking for is the formulas for column J-M to get the results in table 2

    Thanks
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: HELP needed for staff rota

    See attached:

    I changed headings to time (7:00, 08:00)

    in J4

    =INDEX($B$3:$I$3,0,MATCH(1,B4:I4,0))

    in K4

    =J4+TIME(COUNTA(B4:I4),0,0)

    in L4

    =IF(COUNTIF(B4:I4,"LE"),"12:00",IF(COUNTIF(B4:I4,"LL"),"12:30",0))

    in M4

    =(K4-J4)*24-SUM((COUNTIF(B4:I4,{"LE","LL"})))*0.5
    Attached Files Attached Files
    Last edited by JohnTopley; 11-14-2017 at 01:38 AM.

  5. #5
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    184

    Re: HELP needed for staff rota

    Thanks for your reply, in parts what you changed works but there are still issues. Firstly I would like to keep the formatting of the times in row 3 if possible, and also the lunch break only displays as 12:00 or 12:30 even if the break is taken at a different times.

    My upload is a small example of what I actually require and perhaps gives false representation of what I need. I have uploaded the actual workbook I am working on with sensitive information removed.

    Sheet 1 showing every week day where data is entered, each day other than Monday pulling data from Monday except for the hours worked.
    Sheet 2 pulling data from sheet 1 to make an auto completed printable rota for the week.

    So far columns V & Y are showing correct information and its W & X that require new formulas, however V & Y may have to change subject to W & X.

    I hope this makes sense.

    Again thanks.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: HELP needed for staff rota

    See if the following is helpful.
    1) Populate sheet 1 column W with the formula: =IFERROR(VALUE(V3)/24+Y3/24,"")
    2) Set the format of column W to: h:mm
    3) Set the format of sheet 2 column D to: h:mm
    What values do you want displayed in sheet 1 column X?
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    184

    Re: HELP needed for staff rota

    Thanks for your reply.
    Based on the latest example I uploaded, populating column W with your formula results in W5 calculating 14:30 and W13 calculating 13:30, these should be 15:00 and 14:00 respectively. These are to represent the finish time of the shift, all formulas I have tried so far result in taking the break time from the end time and therefore calculating a finish time which is half an hour early.

    In column X I am looking for the break time to be displayed, i.e. "LE" (Lunch Early) in J5 to display 12:00 in X5. "LE" in L5 to display 14:00 and so on.
    If "LL" (Lunch Late) is entered in J5 then 12:30 is displayed in X5 but if "LL" is entered in to L5 then 14:30 is displayed, and so on.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: HELP needed for staff rota

    See if the following are helpful:
    The time intervals in row 2 are changed to 24 hour time (i.e. 1-2 changed to 13-14 etc).
    For V3 (and down):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For W3 (and down):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For X3 (and down):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The range V3:X35 is formatted h:mm;@
    Let us know if you have any questions.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    184

    Re: HELP needed for staff rota

    JeteMc you are a legend. This seems to work just how I wanted it to. Thanks

    Now on to sorting other formatting issues, look out for new posts

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: HELP needed for staff rota

    Just a comment as my previous post: this would have been much easier if you had used proper times rather than TEXT values.

    Persisting with this thinking just makes life unnecessarily difficult and is bad practice.

  11. #11
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    184

    Re: HELP needed for staff rota

    What does ease or bad practice have to do with anything? Someone took the time to fix my problem in the format I required, there are reasons I required that format and the results work faultlessly (something you failed to achieve!)

    Winds me up when people come to forums for help and end up getting replies like your last one, how is it helpful!!!

  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. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: HELP needed for staff rota

    It depends whether you want to learn how to improve or just apply sticking plaster solutions to poor data - it’s entirely up to you. The assumption here is that people want to learn, so most of them will understand that suggestions regarding improving data layout or formatting ARE helpful.
    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.

  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. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: HELP needed for staff rota

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

  14. #14
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    184

    Re: HELP needed for staff rota

    Much "easier" to post improvements to working formulas, that would surely be how people would learn something. Rather than just trying to suggest bad practice! I will mark as SOLVED , thank you all and again thank you JeteMc

  15. #15
    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. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: HELP needed for staff rota

    Just for completeness, John offered you a solution in post #4. People learn things in all sorts of ways: please do not complain about others offering you constructive criticism (and solutions) in future. Those who help here do so voluntarily and in their free time. Thanks for your cooperation.

  16. #16
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    184

    Re: HELP needed for staff rota

    Post #4 was not a solution as it didn't work. Now lets leave it there.

  17. #17
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: HELP needed for staff rota

    So what are the differences in the attached ..
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    184

    Re: HELP needed for staff rota

    The insertion of Row 1??

  19. #19
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: HELP needed for staff rota

    Purely illustrate use of TIME data rather than TEXT .

  20. #20
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: HELP needed for staff rota

    Glad that I was able to be of help. I hope that everyone has a blessed day.

  21. #21
    Registered User
    Join Date
    11-22-2017
    Location
    London
    MS-Off Ver
    10
    Posts
    2

    Re: HELP needed for staff rota

    I have been reading your posts and thought I would share software answer to complex scheduling. My experience is that you'll save more time in the first day than it takes to set up.Its a tool called WorkGaps you can google it and sign up Its free

  22. #22
    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. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: HELP needed for staff rota

    @an.angell - please be aware that advertising is not allowed here. I see that you have posted the same 'advice' to three different threads where you have contributed nothing else. Please stop doing this. Thanks.
    Last edited by AliGW; 11-22-2017 at 08:55 AM.

  23. #23
    Registered User
    Join Date
    11-22-2017
    Location
    London
    MS-Off Ver
    10
    Posts
    2

    Re: HELP needed for staff rota

    I was just helping as I have been googling for the same problems. But thats fine, I wont share my knowledge (also this isnt advertising .. just something that has worked for me)

  24. #24
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    184

    Re: HELP needed for staff rota

    Thanks an.angell, I'll look it up. Keep sharing your knowledge

  25. #25
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    184

    Re: HELP needed for staff rota

    JeteMc, I have encountered an issue and need more help.

    I've attached an example again, the total hours and lunch break columns are working fine but for the start and finish times. When I enter 0.5 to symbolize half an hour it is counted as a full hour.

    W7 should read 14:30
    V9 should read 11:30

    The example should make sense when you see it
    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. [SOLVED] how do I get my staff rota to add number of staff on duty
    By dougers1 in forum Excel General
    Replies: 2
    Last Post: 07-20-2017, 05:18 AM
  2. staff rota
    By dougers1 in forum Excel General
    Replies: 4
    Last Post: 11-11-2015, 07:19 PM
  3. Replies: 14
    Last Post: 05-23-2015, 06:26 AM
  4. Staff rota
    By chelfox in forum Excel General
    Replies: 1
    Last Post: 10-07-2013, 11:25 AM
  5. Staff Rota
    By parkey5 in forum Excel General
    Replies: 5
    Last Post: 07-14-2013, 04:16 AM
  6. Creating a staff rota
    By daustin3 in forum Excel General
    Replies: 0
    Last Post: 05-16-2012, 12:30 PM
  7. Staff rota
    By goofy14you in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-12-2007, 04:13 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