+ Reply to Thread
Results 1 to 21 of 21

Difference between two timestamps within same cell

  1. #1
    Registered User
    Join Date
    02-08-2013
    Location
    Malmö, Sweden
    MS-Off Ver
    Excel 2013
    Posts
    25

    Difference between two timestamps within same cell

    Hi there!

    I know there's a lot of time counting threads, but I couldn't find any threads that answers my question. I want to know how to calculate the time when there's two "timestamps" in the same cell?

    Like this:
    A1: "09:00-16:15"
    A2: "16:00-22:00"
    A3: SUM total time A1+A2

    B1: "06:30-14:30"
    B2: "10:00-12:00"
    B3: SUM total time B1+B2

    If it's useful to know - the time period in the cells is never covered over two days (only 00:01 -> 23:59).

    Thanks!
    Last edited by Jooakim; 10-20-2013 at 07:25 AM.

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,732

    Re: Another time count thread

    Are the quotation marks in your dataset or is this just there for presentation purposes?
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: Another time count thread

    If there is a - seperating the times, I would use Text2Columns to split that into 2 columns to make it easier to work with. We can put a formula together to break them apart, but T2C will be simpler
    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

  4. #4
    Registered User
    Join Date
    02-08-2013
    Location
    Malmö, Sweden
    MS-Off Ver
    Excel 2013
    Posts
    25

    Re: Another time count thread

    Quote Originally Posted by alansidman View Post
    Are the quotation marks in your dataset or is this just there for presentation purposes?
    They are just for presentation purposes.

    Edit: Also, what is your favourite common font for using the worksheet space best?
    Last edited by Jooakim; 10-19-2013 at 05:53 PM.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: Another time count thread

    Maybe try this...
    =SUMPRODUCT(RIGHT(A1:A2,5)-LEFT(A1:A2,5))
    format as time, if needed

  6. #6
    Registered User
    Join Date
    02-08-2013
    Location
    Malmö, Sweden
    MS-Off Ver
    Excel 2013
    Posts
    25

    Re: Another time count thread

    Quote Originally Posted by FDibbins View Post
    Maybe try this...
    =SUMPRODUCT(RIGHT(A1:A2,5)-LEFT(A1:A2,5))
    format as time, if needed
    I had to change "A2,5" to "A2;5", but it says that "22:15-14:00" = 0,34375...

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: Another time count thread

    yes and that is telling you that the answer is 0.34375...of 1 day, in other words, 8 hours and 15 minutes

    you need to format as time (maybe 37:30:55 would be a good option?)

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: Another time count thread

    I should have said this up front, my bad, sorry...You need to change your thread title to something that more closely describes your problem

    To change a Title on your post, click EDIT on you're 1st post, then Go Advanced and change your title

  9. #9
    Registered User
    Join Date
    02-08-2013
    Location
    Malmö, Sweden
    MS-Off Ver
    Excel 2013
    Posts
    25

    Re: Another time count thread

    Quote Originally Posted by FDibbins View Post
    yes and that is telling you that the answer is 0.34375...of 1 day, in other words, 8 hours and 15 minutes

    you need to format as time (maybe 37:30:55 would be a good option?)
    I didn't get the 37:30:55 - is that a random number you picked? And what should I format as time? The columns with the timestamps or something in the function?

    0,34375*24=8,25
    => 0,25*60 = 15

    But I used:
    =TEXT(SUMPRODUCT(RIGHT(A1:A2;5)-LEFT(A1:A2;5));"t:mm")
    And it worked out great.

    Edit: Also, what is your favourite common font for using the worksheet space best?
    Last edited by Jooakim; 10-20-2013 at 07:42 AM.

  10. #10
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Difference between two timestamps within same cell

    37:30:55 used to be a format option in time, it is the same as custom format [h]:mm:ss
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  11. #11
    Registered User
    Join Date
    02-08-2013
    Location
    Malmö, Sweden
    MS-Off Ver
    Excel 2013
    Posts
    25

    Re: Difference between two timestamps within same cell

    Quote Originally Posted by martindwilson View Post
    37:30:55 used to be a format option in time, it is the same as custom format [h]:mm:ss
    Ah, ok.

    Edit: Can you explain this? I might be a little tired...
    # A B C D E
    1 07:00-11:00 07:00-11:00 10:00-14:00 09:00-13:00 07:00-13:00
    2 06:00-14:30 day off 06:30-14:30 sick 06:30-14:30
    3 06:30-15:00 06:30-14:30 06:30-14:30 06:30-14:30 06:30-14:30
    =TEXT(SUMPRODUCT(RIGHT(A1:A3;5)-LEFT(A1:A3;5));"t:mm") = 21:00
    =TEXT(SUMPRODUCT(RIGHT(A1:E1;5)-LEFT(A1:E1;5));"t:mm") = #VALUE!

    I don't understand the logic that the first function works and gets the correct value, but the second function equals #VALUE!
    Also, how would you sum the hours for coulmn B or D when it says "day off" and "sick" on two of the columns? Something like a "if(stringlength == 11) => check hours"? (unfortunately, I don't know that many Excel functions. :p
    Last edited by Jooakim; 10-20-2013 at 02:19 PM.

  12. #12
    Forum Contributor Steve N.'s Avatar
    Join Date
    12-22-2011
    Location
    USA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    298

    Re: Difference between two timestamps within same cell

    Will this work for you?

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


    Steve

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,777

    Re: Difference between two timestamps within same cell

    Maybe it's because the values from A1 to E1 add up to more than 24 hours - maybe you need to do this:

    =TEXT(SUMPRODUCT(RIGHT(A1:E1;5)-LEFT(A1:E1;5));"[t]:mm")

    where the square brackets allow hours above 24 to be displayed, rather than wrapping them at 24.

    Hope this helps.

    Pete

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: Difference between two timestamps within same cell

    Quote Originally Posted by martindwilson View Post
    37:30:55 used to be a format option in time, it is the same as custom format [h]:mm:ss
    Martin, I use 2007 and that is still there as a formatting option under "time"?

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: Difference between two timestamps within same cell

    Based on the sample you proved (post #11)...
    A
    B
    C
    D
    E
    F
    1
    # A B C D E
    2
    1 07:00-11:00 07:00-11:00 01:00-23:00 09:00-13:00 07:00-13:00
    3
    2 06:00-14:30 06:30-14:30 06:30-14:30 sick 06:30-14:30
    4
    3 06:30-15:00 06:30-14:30 06:30-14:30 06:30-14:30 06:30-14:30
    5
    Format
    6
    General---->
    0.875
    0.833333333
    1.583333333
    #VALUE!
    0.916666667
    7
    Time 37:30:55---->
    21:00:00
    20:00:00
    38:00:00
    #VALUE!
    22:00:00
    8
    =SUMPRODUCT( RIGHT(B2:B4,5)- LEFT(B2:B4,5))


    Im still working on when you have "sick" etc

  16. #16
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Difference between two timestamps within same cell

    37:30:55, not in my version it's a regional thing
    Attached Images Attached Images

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: Difference between two timestamps within same cell

    hmm OK thats really interesting, thanks for the feedback, thats good to remember

  18. #18
    Registered User
    Join Date
    02-08-2013
    Location
    Malmö, Sweden
    MS-Off Ver
    Excel 2013
    Posts
    25

    Re: Difference between two timestamps within same cell

    Quote Originally Posted by FDibbins View Post
    hmm OK thats really interesting, thanks for the feedback, thats good to remember
    I hope you don't forget to find a solution to the "sick"/"day off" (or something) problem :p

  19. #19
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Difference between two timestamps within same cell

    =SUM(IF(ISNUMBER(FIND(":",A1:A3)),RIGHT(A1:A3,5)-LEFT(A1:A3,5),0)) array entered would give
    21:00:00 12:00:00 20:00:00 12:00:00 22:00:00

  20. #20
    Registered User
    Join Date
    02-08-2013
    Location
    Malmö, Sweden
    MS-Off Ver
    Excel 2013
    Posts
    25

    Re: Difference between two timestamps within same cell

    Quote Originally Posted by martindwilson View Post
    =SUM(IF(ISNUMBER(FIND(":",A1:A3)),RIGHT(A1:A3,5)-LEFT(A1:A3,5),0)) array entered would give
    21:00:00 12:00:00 20:00:00 12:00:00 22:00:00
    Thanks a lot the help!

    I had to add TIME() around it, but it worked out great! Another thing:
    How do I add f.i. five hours "manually"? I tried to put "+05:00" some places, but it wouldn't work.

  21. #21
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Difference between two timestamps within same cell

    to add 5 hours to a real time
    either(where a1 contains the time value)
    =a1+"05:00" or =a1+5/24

+ 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. Max drawdown and recovery time - New Thread
    By anujamarathe in forum Excel General
    Replies: 3
    Last Post: 11-27-2012, 03:47 AM
  2. Question about card count thread
    By raveepoojari in forum Excel General
    Replies: 2
    Last Post: 05-08-2009, 10:13 AM

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