+ Reply to Thread
Results 1 to 12 of 12

using a timestamp to identify work shifts

  1. #1
    Registered User
    Join Date
    01-24-2011
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    74

    using a timestamp to identify work shifts

    Okay, I've searched - the words "timestamp", "work" and "shift" return way to much to sift through, so I'll start a new thread. Apologies.

    I have a data file that I import into Excel (2007). It gives me a time stamp on each transaction, along with user names, and the transaction details. I need to 'lump' these into the activity performed by each shift. (there are three, from 9pm to 5am, 5am to 1pm, and 1pm to 9pm).

    So, simply stated, did each transaction occur on shift 1, 2, or 3?
    This task is to help management even out workloads across the shifts, and determine peak activities - which I can get to with pivot tables, etc after I've identified the shift. (Since 3rd shift spans two days, the if "window" might be: 9p to midnight, and midnight to 5a ?)

    sample file attached. Staff names deleted.

    any and all help sincerely appreciated !!!

    Refill Activity Shift Test.xlsx
    Last edited by Hang Glider; 12-06-2013 at 04:55 PM.
    < Click the * to say 'thanks'

  2. #2
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111

    Re: using a timestamp to identify work shifts

    G'day

    Does this help ?

    Copy and paste into L2 and copy drag down.

    Please Login or Register  to view this content.
    Have I made you happy ??? If yes, please make me happy by pressing the http://www.excelforum.com/images/buttons/reputation-40b.png Add Reputation button in my post.
    Please don't forget to do the same to other contributors of this forum.

    Thanks
    I don't void confusion, I create it

  3. #3
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: using a timestamp to identify work shifts

    Hi ratcat

    Looking at your code trying to understand the numbers used in your formula I would have thought that based on the OP’s information about the different shifts numbers should be:

    Shift 1 from 00:00 to 01:00 factor < 1/24
    Shift 2 from 01:00 to 09:00 factor >= 1/24 and < 9/24
    Shift 3 from 09:00 to 17:00 factor >= 9/24 and < 17/24
    Shift 1 from 17:00 to 00:00 factor >= 17/24

    Using a helper column L for the =MOD(G2,1) function and formatting this as Custom “hh:mm” my final formula in the “M” column (shift result) is
    Please Login or Register  to view this content.
    But as Scandinavia uses the 24 hr time setting instead of PM and AM this could be the reason I can’t “translate” the numbers you use.

    Alf

  4. #4
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111

    Re: using a timestamp to identify work shifts

    G'day Alf,

    The numbers you refer are the factor you talk about but I have my factor in decimal form. This make it easier for me to work with. Also I believe a lot more accurate.

    The cell that's displaying the time (Time format cell) in it, change the format of that cell to 'General' and it will display the decimal number.

    Does that help ?

  5. #5
    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,917

    Re: using a timestamp to identify work shifts

    Just my 2c' worth...I prefer to see the actual time calc in the formula. 5/24 tells me it's 5 hours, but 0.208333 is not so easy to identify as being 5 hours.

    Also, using 5/24 (0.2083333333333330) is actually more accurate than 0.208333, although that level of accuracy can cause its own problems later on
    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

  6. #6
    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,917

    Re: using a timestamp to identify work shifts

    duplicate post deleted

  7. #7
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: using a timestamp to identify work shifts

    Hi ratcat

    Thanks for the info about your formula. I was curious since the OP requested time intervals going
    from 9pm to 5am, 5am to 1pm, and 1pm to 9pm
    and I could not “translate” your numbers to match this request.

    Just out of curiosity could you test this 24 hour formula in a PM / AM environment and tell me if this works?
    Please Login or Register  to view this content.
    Alf

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: using a timestamp to identify work shifts

    I used a vlookup table to convert the times to shifts:
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  9. #9
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111

    Re: using a timestamp to identify work shifts

    G'day Alf,

    That formula you asked me to test works but does not work for the OP request.

    As stated in the workbook....

    (if between 9pm to 5am, then shift "3", if between 5am to 1pm, then shift "1", if between 1pm to 9pm, then shift "2")
    If you modify my formula back to faction of time looks like this

    Please Login or Register  to view this content.
    Does that help ?

  10. #10
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: using a timestamp to identify work shifts

    Thanks ratcat!
    For the future I'll stick to the 24 hour system when answerig threds.
    Alf

  11. #11
    Registered User
    Join Date
    01-24-2011
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    74

    Re: using a timestamp to identify work shifts

    These are wonderful - I am grateful for your knowledge, all of you!

  12. #12
    Registered User
    Join Date
    02-28-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    100

    Re: using a timestamp to identify work shifts

    Interesting...

+ 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. Work Schedule, 10hr Shifts, 4 on 3 off, Rotating Weekends Off
    By onflight1978 in forum Excel General
    Replies: 5
    Last Post: 12-21-2012, 03:38 PM
  2. Work shifts
    By gibson2503 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-16-2012, 09:08 AM
  3. Cannot get timestamp to work with macro correctly
    By gsx1000r02 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-07-2012, 11:59 AM
  4. IF Formula working with work shifts
    By DGutterud in forum Excel General
    Replies: 5
    Last Post: 05-19-2011, 08:23 PM
  5. A function that separates hours worked in work shifts that overlap
    By Katybug1964 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-24-2005, 05:06 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