Difference between two timestamps within same cell

[Jooakim]

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!

[alansidman]

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

[FDibbins]

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

[Jooakim]

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?

[FDibbins]

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

[Jooakim]

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

[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?)

[FDibbins]

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

[Jooakim]

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?

[martindwilson]

37:30:55 used to be a format option in time, it is the same as custom format [h]:mm:ss

[Jooakim]

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

[Steve N.]

Will this work for you?

Please Login or Register  to view this content.

Steve

[Pete_UK]

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

[FDibbins]

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"?

[FDibbins]

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

[martindwilson]

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

[FDibbins]

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

[Jooakim]

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

[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

[Jooakim]

=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.

[martindwilson]

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