CODEWITHSUNDEEP
excel
Ivan Shatsky
Ivan Shatsky

Reputation: 15527

Summing results of some formula applied to each cell of column

I have the following data in my excel table:

                01.04.2020  02.04.2020  ...
Employee 01     
Employee 02                 7:31-16:39
Employee 03     8:02-17:04  
Employee 04
Employee 05
Employee 06     5:37-8:21   5:31-8:06
Employee 07     8:02-19:42  
Employee 08                 8:11-21:44
Employee 09     9:48-19:45  8:40-21:05
Employee 10     9:40-19:45  
Employee 11     
Employee 12                 7:47-16:42
Employee 13     
Employee 14     
Employee 15     9:52-19:43  9:51-20:00

I've managed to calculate total time spent by each employee with the following formula:

=TIMEVALUE(RIGHT(B2;LEN(B2)-FIND("-";B2)))-TIMEVALUE(LEFT(B2;FIND("-";B2)-1))

But I can't figure out how can I calculate the sum of this formula results for each day. Can it be done with excel without adding additional columns?

Upvotes: 0

Views: 70

Answers (2)

moises mera
moises mera

Reputation: 161

I was able to do it once with a column and once without the extra column as per your request. Generally you need to use keystroke CTRL+SHIFT+ENTER in order to make this work but because we used SUMPRODUCT formula this keystroke was removed. Picture is attached and formula below. =SUMPRODUCT( IFERROR( TIMEVALUE( RIGHT(C2:C16, LEN(C2:C16)-FIND("-", C2:C16)) )- TIMEVALUE( LEFT(C2:C16,FIND("-",C2:C16)-1) ), 0) *24) enter image description here

Upvotes: 0

teylyn
teylyn

Reputation: 35915

Try (for regional settings using commas in formulas)

=SUM(IFERROR(TIMEVALUE(RIGHT(B2:B16,LEN(B2:B16)-FIND("-",B2:B16)))-TIMEVALUE(LEFT(B2:B16,FIND("-",B2:B16)-1)),0))

And for your regional settings that use semicolons instead of commas)

=SUM(IFERROR(TIMEVALUE(RIGHT(B2:B16;LEN(B2:B16)-FIND("-";B2:B16)))-TIMEVALUE(LEFT(B2:B16;FIND("-";B2:B16)-1));0))

In Office 365 with Dynamic Arrays that formula will work by confirming with just the Enter key. I'm not sure if it will work in older versions of Excel at all, but if so, only after confirming with Ctrl+Shift+Enter.

Upvotes: 1

Related Questions