# Difference between two timestamps within same cell

1. ## 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!

2. ## Re: Another time count thread

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

3. ## 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

4. ## Re: Another time count thread

Originally Posted by alansidman
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?

5. ## Re: Another time count thread

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

6. ## Re: Another time count thread

Originally Posted by FDibbins
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. ## 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. ## 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. ## Re: Another time count thread

Originally Posted by FDibbins
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?

10. ## 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

11. ## Re: Difference between two timestamps within same cell

Originally Posted by martindwilson
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

12. ## Re: Difference between two timestamps within same cell

Will this work for you?

Formula:
`Please Login or Register  to view this content.`

Steve

13. ## 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. ## Re: Difference between two timestamps within same cell

Originally Posted by martindwilson
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. ## 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. ## Re: Difference between two timestamps within same cell

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

17. ## Re: Difference between two timestamps within same cell

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

18. ## Re: Difference between two timestamps within same cell

Originally Posted by FDibbins
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. ## 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. ## Re: Difference between two timestamps within same cell

Originally Posted by martindwilson
=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. ## 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

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

#### 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