+ Reply to Thread
Results 1 to 5 of 5

Textual output, based on workers' variable and fixed Start and Finishing Times

  1. #1
    Registered User
    Join Date
    05-25-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2019
    Posts
    27

    Question Textual output, based on workers' variable and fixed Start and Finishing Times

    I am trying to create a simple table which graphically shows what type of shift someone is working.
    Basically, against every employee, for each day of the week, I have a Start time in one cell, Finish time in another, with additional cells for an Early start and Late finish.
    I want to be able to show in a separate column for each day, either an "I" for in when the person is working a normal day, i.e. start and finish times only populated.
    However, if that person has an early start, this time would be entered in the early column, in addition to their normal start & finish times. This would produce an "E" for early.
    Similarly, someone working overtime will have a late time entered, in addition to their normal start & finish times - producing an "L" for late.
    If an employee is extending their shift at both ends, all four cells would be populated with appropriate times and an "X" would show for "eXtended" time.
    If it's a full-time employee, their days off would show an "O" for out/off. Part-time staff, would only show days worked, with non-working days blank.

    I'm looking for a simple way of achieving this using Excel VBA.

    I can easily check for blanks using ISBLANK and ">0" for cells that have times in them. However, I'm struggling to come up with a successful formula that produces a single, accurate output of the appropriate letter, i.e. I, O, E, L or X.

    Any assistance with this would be gratefully received. MTIA

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,936

    Re: Textual output, based on workers' variable and fixed Start and Finishing Times

    I'm looking for a simple way of achieving this using Excel VBA.
    why does it have to be VBA?

    Without seeing a sample of what you are working with, I am only guessing here, but perhaps something like a COUNT() would help you? Assuming your Early/Start/Finish/Late are in A:D, maybe something like...
    =IF(count(A2:D2)=4,"X",if(count(A2:C2)=3,"E",if(count(B2:D2)=3,"L","I")))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    05-25-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2019
    Posts
    27

    Re: Textual output, based on workers' variable and fixed Start and Finishing Times

    Hi Ford

    Thanks for your input. There are other elements of the operations scheduling project that rely on VBA control, not mentioned here as they are irrelevant. There are other things like "S" for sick or "H" for holiday that are included. Your method doesn't check anything other than the existence of an entry. However I do like it, as it would be simple and effective.

    The data is captured from a master scheduler and imported as text, mainly "D/O" for day off but there are other text values as mentioned earlier, plus times in hh:mm format. The columns are actually Start/Finish/Early/Late.

    I've attached a simple sample, showing most elements. The text inputs are manual for this example.
    Please feel free to try out with VBA. Note: I don't want any formulae on the spreadsheet itself.

    Many thanks!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,936

    Re: Textual output, based on workers' variable and fixed Start and Finishing Times

    If you dont want any formulas (why?) then VBA is the only way to go for this (and VBA is not my strong suite)

  5. #5
    Registered User
    Join Date
    05-25-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2019
    Posts
    27

    Thumbs up Re: Textual output, based on workers' variable and fixed Start and Finishing Times

    It's OK. I've managed to solve this myself. I found a great function that I didn't know existed, i.e. IsEmpty(Value). So I used logic AND combinations with this function and by checking the cell's timevalue >0 or text content, with IF..THEN statements, I managed to get it working 100% the way I wanted . Thanks for your contribution Ford. Your responses are much appreciated .
    Back to solving the rest of my project issues...

+ 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. Replies: 21
    Last Post: 05-11-2015, 03:53 PM
  2. Replies: 0
    Last Post: 03-16-2015, 01:50 PM
  3. [SOLVED] Update cell value based on fixed and variable value
    By The Phil in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-08-2014, 08:27 PM
  4. [SOLVED] Sum column based on variable start and variable end months
    By Steve N. in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-06-2013, 08:01 PM
  5. Replies: 2
    Last Post: 11-22-2011, 12:33 PM
  6. Replies: 0
    Last Post: 11-14-2011, 05:40 PM
  7. Excel 2007 : Headcount Based on Date & Start/Stop Times
    By ibspayroll in forum Excel General
    Replies: 8
    Last Post: 05-10-2011, 05:28 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