Formula to calculate total number of hours in a single cell
Results 1 to 6 of 6

Thread: Formula to calculate total number of hours in a single cell

  1. #1
    New Member
    Join Date
    Oct 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula to calculate total number of hours in a single cell

    Hi,

    I am currently writing a roster, due to the number of employees itís important we keep the spreadsheet as compact as possible, hence the question.

    I currently have a formula to calculate number of hours worked in one cell ie 10:15-18:30
    Formula as follows;
    =IF(TIMEVALUE(LEFT(B3,SEARCH("-",B3)-1))>TIMEVALUE(RIGHT(B3,LEN(B3)-SEARCH("-",B3))),DATEVALUE("24:00:00")-(TIMEVALUE(LEFT(B3,SEARCH("-",B3)-1))-TIMEVALUE(RIGHT(B3,LEN(B3)-SEARCH("-",B3)))), TIMEVALUE(RIGHT(B3,LEN(B3)-SEARCH("-",B3)))-TIMEVALUE(LEFT(B3,SEARCH("-",B3)-1)))

    Firstly, I can format the cell to display number of hours worked as 8:15 however I need it to read 8.25 hours, how can I format this within the same cell?

    Secondly, how can I adapt this formula to calculate total number of hours worked in 1 week, taking into account days off (as I receive an error)?

    Ie
    10:15-14:30 off off 10:30-18:30 9:30-16:30 9:00-4:00

    Thanks in advance for any assistance you can provide.

    Ps. Itís important the formula can detect 15 minute increments

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Formula to calculate total number of hours in a single cell

    Welcome to Mr Excel forum

    Maybe this...

    A
    B
    C
    D
    E
    F
    G
    H
    1
    Day 1
    Day 2
    Day 3
    Day 4
    Day 5
    Day 6
    Day 7
    Total Hours
    2
    10:15-18:30
    10:15-14:30
    off
    off
    10:30-18:30
    9:30-16:30
    9:00-4:00
    46,5


    Array formula in H2
    =SUM(IF(IFERROR(TIMEVALUE(LEFT(A2:G2,SEARCH("-",A2:G2)-1)),0)>IFERROR(TIMEVALUE(RIGHT(A2:G2,LEN(A2:G2)-SEARCH("-",A2:G2))),0),IFERROR(TIMEVALUE(RIGHT(A2:G2,LEN(A2:G2)-SEARCH("-",A2:G2))),0)+1,IFERROR(TIMEVALUE(RIGHT(A2:G2,LEN(A2:G2)-SEARCH("-",A2:G2))),0))-IFERROR(TIMEVALUE(LEFT(A2:G2,SEARCH("-",A2:G2)-1)),0))*24

    confirmed with Ctrl+Shift+Enter, not just Enter

    Hope this helps

    M.

  3. #3
    New Member
    Join Date
    Oct 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to calculate total number of hours in a single cell

    Thank you!
    Will trial the formula and let you know!

    Fran

  4. #4
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Formula to calculate total number of hours in a single cell

    Quote Originally Posted by Fb1989 View Post
    Thank you!
    Will trial the formula and let you know!

    Fran
    You are welcome. Hope it works.

    BTW, i think you should consider a different data setup because the formula would be much simpler. Something like

    A
    B
    C
    D
    E
    1
    Day
    Start
    End
    Result
    2
    1
    10:15
    18:30
    46,5
    3
    2
    10:15
    14:30
    4
    3
    off
    5
    4
    off
    6
    5
    10:30
    18:30
    7
    6
    09:30
    16:30
    8
    7
    09:00
    04:00


    Array formula E2
    =SUM(IF(ISNUMBER(B2:B8),IF(B2:B8>C2:C8,1+C2:C8,C2:C8)-B2:B8))*24
    Ctrl+Shift+Enter

    M.

  5. #5
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to calculate total number of hours in a single cell

    This formula should work with your original setup

    =SUM(IFERROR(MOD(MID(A2:G2,FIND("-",A2:G2)+1,9)-LEFT(A2:G2,FIND("-",A2:G2)-1),1),0))*24

    confirm with CTRL+SHIFT+ENTER

  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Formula to calculate total number of hours in a single cell

    Quote Originally Posted by barry houdini View Post
    This formula should work with your original setup

    =SUM(IFERROR(MOD(MID(A2:G2,FIND("-",A2:G2)+1,9)-LEFT(A2:G2,FIND("-",A2:G2)-1),1),0))*24

    confirm with CTRL+SHIFT+ENTER
    Very nice!

    M.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •