+ Reply to Thread
Results 1 to 7 of 7

Formula for time displayed within a staff rota

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

    Formula for time displayed within a staff rota

    Hi,

    Reopening a project you guys have previously helped me with, everything you did works well but I am just looking for a slight amendment.

    I have attached the project and removed sensitive data, sheet 2 & 3 are locked as these need no change.

    Sheet 1 Tuesday onward is not needed so again cells are locked.

    This example focuses on sheet 1 cells C3:T38 and V3:W38 and I think its the formulas in columns V&W that need amending.

    V6:Y7 show the results I am looking for but I want this to be true when the data in D9:R10 is used and not the data currently used as in D6:R7.

    Basically it already works exactly as I want except I want,
    0.25 in D6 to result 6.45 in V6
    0.75 in L6 to result 14.45 in W6
    0.75 in J7 to result 12.15 in V7
    0.25 in R7 to result 20.15 inR7

    This is to fall in line with things already used in my workplace, otherwise I would use it as it is.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Formula for time displayed within a staff rota

    With the values applied to D6, L6, J7 & R7 per your summary, the below should return your desired results

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    note: the above do not require Array entry

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Formula for time displayed within a staff rota

    Try this
    In V3 then copied down
    Please Login or Register  to view this content.
    In W3 then copied down
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

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

    Re: Formula for time displayed within a staff rota

    XLent

    Your formula works perfect for getting the required result in the "start" & "finish" time required, but has now altered the "total paid" hours in column Y.

    Looking at the new attachment, ignore the input for C45:T80, this is just to show the desired results in V45:Y80.
    In the Tuesday example this shows the correct results for "start", "finish", "lunch" & "total paid".
    In the Monday example using your formula the "start", "finish" & "lunch" results are now as required but the "total paid" is wrong when using 0.25 & 0.75.

    Looking at the legend on sheet 3, LE & LL represent a half hour unpaid break, so this would be deducted from the total hours worked.

    I also tried adding IFERROR to remove unwanted data in empty cells like in V45:Y80 but this didn't work.

    Thanks
    Tez
    Attached Files Attached Files

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

    Re: Formula for time displayed within a staff rota

    kvsrinivasamurthy

    Your suggestions still gave an incorrect time, entering 0.25 in D6 resulted in 6.15 start when I need this to be 6.45.

    Thanks anyway.

  6. #6
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Formula for time displayed within a staff rota

    OK, to break this into two pieces

    1. to remove unwanted errors

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    2. for hours worked... I might be over simplifying here but... is it not simply?

    Formula: copy to clipboard
    Please Login or Register  to view this content.

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

    Re: Formula for time displayed within a staff rota

    XLent, you are a star.

    Works perfectly for what I needed to achieve. Thank you.

    Its funny how fixing one problem creates another in Excel lol, I now have issues in column B but that's another story which I'll try and fix. I'm really starting to love Excel and how powerful it is.

    Again thank you!

+ 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. Replies: 14
    Last Post: 05-23-2015, 06:26 AM
  3. Staff rota
    By chelfox in forum Excel General
    Replies: 1
    Last Post: 10-07-2013, 11:25 AM
  4. [SOLVED] Formula Query for Staff Rota
    By bigtiger1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-02-2013, 07:33 AM
  5. [SOLVED] Formula for male and female staff on rota
    By bigtiger1 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 08-06-2013, 07:03 AM
  6. [SOLVED] A formula to work out hours on a staff rota
    By bigtiger1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-30-2013, 07:23 PM
  7. Replies: 0
    Last Post: 06-12-2013, 03:33 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