+ Reply to Thread
Results 1 to 9 of 9

Program to keep track of work shifts

  1. #1
    Registered User
    Join Date
    09-14-2018
    Location
    California
    MS-Off Ver
    Excel 2016
    Posts
    40

    Program to keep track of work shifts

    Hi, I'm working on a program to keep track of work shift based on different inputs. I will have the department in which the work was done, on what shift and day it started, how many hours it took to get done. Depending on the department it will display on a cell the color associated with it. I keep getting stuck in this and need some guidance. I'm trying to avoid VBA, I know this can probably be done with formulas and conditional formatting. Here's a mock up of what I'm trying to do.

    For example: if a work was done in QC, it started on 3/4/19 in Day shift and it took 40 hours to get done. Then cells J8 through N8 will be colored red because that's the color of QC. Each cell there is 8 hours, which is why only 5 cells are needed. If the work had been done in 42 hours, then 6 cells should be red because the last 2 hours were worked during grave shift on 3/6/19

    Capture1000.JPG
    Last edited by owtu; 03-12-2019 at 11:15 AM.

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

    Re: Program to keep track of work shifts

    well, you might find it easier (both short & long term) to return a # to the cells in your matrix (I6 onwards)

    the above means your CF rule can be based on the number in the cell, this will also future proof against calculations you may choose to do latterly based on "colour".

    with the above in mind... based on your screenshot (files are much better as people don't have to guess at cell references / contents, particular when using merged cells)

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

    you should then find you have 0s, 1s, 2s & 3s... where 0 is "nothing", 1 is Engineering activity, 2 is PM activity and 3 is QC activity.

    if you wish to, apply a custom number format to this range of ;;;@ and the numbers (schedule key) will disappear from view (they're still there, obviously)

    then apply your CF rule based on cell value = 1, 2, 3 etc...

    edit: for ease of reference I've added a working example with the above in place...
    Attached Files Attached Files
    Last edited by XLent; 03-12-2019 at 12:10 PM.

  3. #3
    Registered User
    Join Date
    09-14-2018
    Location
    California
    MS-Off Ver
    Excel 2016
    Posts
    40

    Re: Program to keep track of work shifts

    Thank you XLent. It works but whenever I click on the formula for any cell it stops working and instead of displaying the color it just display the formula as if it were a comment. What's causing that?

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

    Re: Program to keep track of work shifts

    Sorry, I confess I don't quite understand... if you can, post an image of the behaviour.

  5. #5
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Program to keep track of work shifts

    formulas showing.PNG

    I believe this is what owtu is referring to. If you edit one of the formula cells, then hit enter, the formula shows up rather than the conditional formatting. I think this has to do with the custom cell format. The only solution I can think of is to change the format back to general, and set the font color the same as the fill color in the conditional formatting so the numbers remain "hidden".

  6. #6
    Registered User
    Join Date
    09-14-2018
    Location
    California
    MS-Off Ver
    Excel 2016
    Posts
    40

    Re: Program to keep track of work shifts

    Yes, Melvosh. That's precisely what I mean. I tried changing the format to general and it didn't work either.

  7. #7
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Program to keep track of work shifts

    Try the attached file. If it works, please keep in mind that XLent did all the hard work!

  8. #8
    Registered User
    Join Date
    09-14-2018
    Location
    California
    MS-Off Ver
    Excel 2016
    Posts
    40

    Re: Program to keep track of work shifts

    Thank you so much XLent, it works! And thank you Melvosh for finding the bug. Why was it doing that? What did you change?

  9. #9
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Program to keep track of work shifts

    Quote Originally Posted by owtu View Post
    And thank you Melvosh for finding the bug. Why was it doing that? What did you change?
    I actually just changed the cell format to General, and changed the conditional formatting to change the font color to the same color as the fill color. I think the problem was the custom cell format, but I'm not 100% sure.

+ 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] Saturday Work Schedule - 28 Advisors - 8 Shifts
    By SVTF in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-22-2018, 12:12 AM
  2. [SOLVED] If statement help: formula to calculate work shifts
    By nvandyken in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-22-2015, 09:44 AM
  3. Work Schedule - 24/7 - 4 shifts per day, 6 people
    By as519999 in forum Excel General
    Replies: 3
    Last Post: 04-06-2015, 07:33 PM
  4. [SOLVED] using a timestamp to identify work shifts
    By Hang Glider in forum Excel General
    Replies: 11
    Last Post: 12-09-2013, 03:43 AM
  5. Track employees which cross shifts and counted one time each shift
    By Macileo in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 10-24-2013, 02:20 AM
  6. Work shifts
    By gibson2503 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-16-2012, 09:08 AM
  7. IF Formula working with work shifts
    By DGutterud in forum Excel General
    Replies: 5
    Last Post: 05-19-2011, 08:23 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