+ Reply to Thread
Results 1 to 8 of 8

Number from rota time sheet into grid using time

  1. 05-03-2015, 11:42 AM #1
    Fordy_deluxe
    Fordy_deluxe is offline
    Registered User
    Join Date
    04-29-2015
    Location
    Newport, Wales
    MS-Off Ver
    2007
    Posts
    17

    Number from rota time sheet into grid using time

    Hi all,

    Having a problem which seems to surpass my knowledge of excel...

    Attached is a document with 2 work sheets, 1 is my works rota, the other is a grid sheet. What i'm looking to achieve from this is as follows:

    If somebody works a 9-5 shift for example, on the rota page you would select the 9 until 5 from the drop down boxes....Then, automatically, on the grid page id like the grid between 09:00-09:59 and 16:00-16:59 to be marked in some way, ideally with a 1 or a colour or something...

    Each day with have a separate grid btw...iv just used Sunday as an example.

    Is this a possibility? Using this data it would then feed into another work sheet to do something else with the data.

    Banging my head against a wall with this one.

    Thanks in advance...
    Attached Files Attached Files
    Last edited by Fordy_deluxe; 05-03-2015 at 12:04 PM. Reason: Added attachement
    Reply With QuoteRegister To Reply
  2. 05-03-2015, 12:04 PM #2
    Nisha Dhawan
    Nisha Dhawan is offline
    Valued Forum Contributor
    Join Date
    05-07-2014
    Location
    India
    MS-Off Ver
    MS Office 365
    Posts
    313

    Re: Number from rota time sheet into grid using time

    Hi Fordy,

    The Attachment is missing. Can you please upload once again?
    Thanks
    Nisha Dhawan


    If you like my answer please click on * Add Reputation
    "If you can dream it, You can do it "
    Reply With QuoteRegister To Reply
  3. 05-03-2015, 12:13 PM #3
    Fordy_deluxe
    Fordy_deluxe is offline
    Registered User
    Join Date
    04-29-2015
    Location
    Newport, Wales
    MS-Off Ver
    2007
    Posts
    17

    Re: Number from rota time sheet into grid using time

    It wouldnt upload on my company laptop i just had to send it to my persoanl laptop, i have ammeded the original post now...Thanks
    Reply With QuoteRegister To Reply
  4. 05-03-2015, 01:02 PM #4
    jhren
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Number from rota time sheet into grid using time

    Perhaps this will get you headed in the desired direction.

    Rota Grid B3:
    Please Login or Register  to view this content.
    Not sure how to correlate between sheets. You have days of week on one, and what appears to be days of month on the other...???
    Reply With QuoteRegister To Reply
  5. 05-03-2015, 04:14 PM #5
    Fordy_deluxe
    Fordy_deluxe is offline
    Registered User
    Join Date
    04-29-2015
    Location
    Newport, Wales
    MS-Off Ver
    2007
    Posts
    17

    Re: Number from rota time sheet into grid using time

    Il give that a try, The numbers down the side were actually just numbers of staff, so if 2 staff started at 9 two slots would be filled in etc
    Reply With QuoteRegister To Reply
  6. 05-03-2015, 07:23 PM #6
    jhren
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Number from rota time sheet into grid using time

    If it were my sheet, I'd want it to report staff type present so I'd know whether there are any gaps.
    Reply With QuoteRegister To Reply
  7. 05-04-2015, 03:28 AM #7
    Fordy_deluxe
    Fordy_deluxe is offline
    Registered User
    Join Date
    04-29-2015
    Location
    Newport, Wales
    MS-Off Ver
    2007
    Posts
    17

    Re: Number from rota time sheet into grid using time

    Just had a play around with it,it works but has no reference to the end time. I uploaded the file again but with an example on the right hand side of what i'm looking to achieve. In the example, 1 person works 9-5, the next, 9-8 and the next 12-6. I understand that likely a more simple formula will need to go into each cell but i'm happy to do that...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 05-05-2015, 04:21 AM #8
    jhren
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Number from rota time sheet into grid using time

    Copy, paste below formula into Rota Grid B3 while in edit mode and confirm entry with Ctrl+Shift+Enter (it's an array formula)... then copy down and right.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. I am trying to link data from a weekly rota to a daily rota, organised by arrival time.
    By paolararo in forum Excel General
    Replies: 0
    Last Post: 04-07-2015, 11:39 PM
  2. create time sheet from rota
    By Managerwork in forum Excel General
    Replies: 0
    Last Post: 10-09-2013, 10:53 AM
  3. [SOLVED] Rota time calculations and colour coding
    By lukela85 in forum Excel General
    Replies: 4
    Last Post: 09-14-2013, 06:28 AM
  4. Rota summary of time gained with summary of time used - pivot two columns from single row
    By godisaclog in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-06-2013, 05:14 AM
  5. [SOLVED] Time sheet, I need a formula for overtime cell into Time 1/2 and double time cells
    By bev in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-18-2013, 11:31 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1