+ Reply to Thread
Results 1 to 17 of 17

Calculate time durations for a roster, and indentify the earliest and latest times

  1. #1
    Forum Contributor
    Join Date
    03-30-2009
    Location
    dublin
    MS-Off Ver
    Excel 2007
    Posts
    104

    Calculate time durations for a roster, and indentify the earliest and latest times

    i have to record time durations for a roster that starts at 07:00 and ends 06:00. please see attached sheet.


    I NEED A FORMULA TO POPULATE THE "START" AND "END" COLUMN

    mANY THANKS IN ADVANCE

    I AM CONSIDERING THIS PARTICULAR LAYOUT BUT IT IS NOT SET IN STONE
    Attached Files Attached Files

  2. #2
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Calculate time durations for a roster, and indentify the earliest and latest times

    Hi bajdr47

    Not sure what you mean!
    Attached Files Attached Files

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

    Re: Calculate time durations for a roster, and indentify the earliest and latest times

    Pl see the attached file.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    03-30-2009
    Location
    dublin
    MS-Off Ver
    Excel 2007
    Posts
    104

    Re: Calculate time durations for a roster, and indentify the earliest and latest times

    Sorry Gentlemen,

    The roster starts at 07:00 and ends 24hrs later 06:00.
    i need to read the time order from 07:00-06:00, not 00:00 to 24:00.
    Also the start and end time will not always start or end on the latest or earliest time

    Regards

    Baj

  5. #5
    Forum Contributor
    Join Date
    03-30-2009
    Location
    dublin
    MS-Off Ver
    Excel 2007
    Posts
    104

    Re: Calculate time durations for a roster, and indentify the earliest and latest times

    Sorry about this,

    i am badly stuck in the sheet attached "ROSTER" i need to examine an indeterminate number of time periods and indentify the earliest and latest time ie EARLIEST = STARTTIME and the LATEST ENDTIME. the difficulty is that the time has to be calculated where the earliest time is 07:00 and the latest time is 06:00. As the Roster starts at 07:00 not at midnight

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate time durations for a roster, and indentify the earliest and latest times

    The roster starts at 07:00 and ends 24hrs later 06:00
    That's 23 hours later.

    Perhaps you could post an updated example, and explain clearly in context what the problem is.
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Forum Contributor
    Join Date
    03-30-2009
    Location
    dublin
    MS-Off Ver
    Excel 2007
    Posts
    104

    Re: Calculate time durations for a roster, and indentify the earliest and latest times

    Sorry again,

    i hope that this example is easier to understand.

    Regards

    baj
    Attached Files Attached Files

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate time durations for a roster, and indentify the earliest and latest times

    What aren't the tasks recorded squentially?

    What is the problem with what you have?

  9. #9
    Forum Contributor
    Join Date
    03-30-2009
    Location
    dublin
    MS-Off Ver
    Excel 2007
    Posts
    104

    Re: Calculate time durations for a roster, and indentify the earliest and latest times

    In the START and END column, i have to manually input the times by checking Cloumns B E and H, i was wondering if there is a formula that will automate the selection in Column J and K

  10. #10
    Forum Contributor
    Join Date
    03-30-2009
    Location
    dublin
    MS-Off Ver
    Excel 2007
    Posts
    104

    Re: Calculate time durations for a roster, and indentify the earliest and latest times

    Sorry SHG,

    the tasks come in from job sheets, and i don't have control of the inputting process.

  11. #11
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate time durations for a roster, and indentify the earliest and latest times

    Shifts run from 07:00, but ann comes in at 6:00?

  12. #12
    Forum Contributor
    Join Date
    03-30-2009
    Location
    dublin
    MS-Off Ver
    Excel 2007
    Posts
    104

    Re: Calculate time durations for a roster, and indentify the earliest and latest times

    Sorry that should have read 08:00

    ---------- Post added at 08:28 PM ---------- Previous post was at 08:25 PM ----------

    Dont always have my glasses on....But i need to always have my glasses on

  13. #13
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate time durations for a roster, and indentify the earliest and latest times

    K2: =MIN(MOD(C2-"7:00",1), MOD(F2-"7:00",1), MOD(I2-"7:00",1)) + "7:00"

    L2: =MAX(MOD(D2-"7:00",1), MOD(G2-"7:00",1), MOD(J2-"7:00",1)) + "7:00"

    Copy down.

  14. #14
    Forum Contributor
    Join Date
    03-30-2009
    Location
    dublin
    MS-Off Ver
    Excel 2007
    Posts
    104

    Re: Calculate time durations for a roster, and indentify the earliest and latest times

    Many thanks SHG,

    There is a problem if any of the tasks are left empty, i adapted your formula as below, it seems to be ok.
    =MIN(IF(C4="",0,MOD(C4-"7:00",1)),IF(F4="",0,MOD(F4-"7:00",1)),IF(I4="",0,MOD(I4-"7:00",1)))+"7:00" IS this OK.or would you have altered it differently.
    If i could be so bold to ask, but i understand in principle how the MOD function works, but would you mind explaining about the use of the 07:00 and particularly the double quotes.

    Again you assistance is very much appreciated

    BAJ

  15. #15
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate time durations for a roster, and indentify the earliest and latest times

    Your shift starts at 7:00. I subtracted 7 hours from all of the times (effectively moving the day back 7 hours), got the minimum, and then added the 7 hours back.

    "7:00" is a string that looks like a time. When it's part of an arithmetic expression, it gets coerced to the number value for 7:00.

  16. #16
    Forum Contributor
    Join Date
    03-30-2009
    Location
    dublin
    MS-Off Ver
    Excel 2007
    Posts
    104

    Re: Calculate time durations for a roster, and indentify the earliest and latest times

    SHG

    Sorry but how should i record a task that ends at the close of the shift eg 04:00 - 07:00 It doesn't seem to register.

    Kind Regards

    BAJ

  17. #17
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate time durations for a roster, and indentify the earliest and latest times

    You would be doing yourself a favor if you posted example workbooks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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