Excel Find Starting and finishing Time

Hello there

I'm having trouble find the right idea for a new project i have and i was wondering if the following is possible. you see i have the fallowing table...


TASK Starting Time Finishing Time TEST
Agendamentos Recolhas 11:15 11:26
Agendamentos Recolhas 11:31 11:40 BREAK
Reagendamentos 11:41 11:45
Reagendamentos 11:46 11:49
Reagendamentos 11:50 11:54 BREAK
Agendamentos Recolhas 15:03 15:12
Agendamentos Recolhas 15:13 15:21
Agendamentos Recolhas 15:22 15:34 BREAK
Confirmacoes Comerciais 16:07 16:08
Confirmacoes Comerciais 16:20 16:41 BREAK
Agendamentos Recolhas 19:00 19:10
Agendamentos Recolhas 19:36 19:46
Agendamentos Recolhas 19:47 20:02 BREAK



There are different task that one person performes througth the day and in most of the cases they are done in alternative ways and not straight foward.

My question is... is it possible that every time that the word "BREAK" appears it counts the first starting and the last finishing time together in a new column? the point is to know when i started and stopped that task.

Am i making sense? sorry for the bad english and for the table... for some reason i canīt add attachs or images...

Thanks in advance

Hi Iceman,

Welcome to the forum.

Try using below formula:-
{=IF(D2="break",MAX(IF($A$2:$A$14=$A2,$C$2:$C$14,""))-MIN(IF($A$2:$A$14=$A2,$B$2:$B$14,"")),"")}

See attached: Starting & End time of task.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi Iceman1988,

Earlier you said in your post#1 that :-

for some reason i canīt add attachs or images...

and now you are able to ... that's good

I have gone through with the attachment and can see only three tasks spread in different time zones ;-

Agendamentos Recolhas
Reagendamentos
Confirmacoes Comercials


How do we tell Excel to recognise if they should be considered more than once ?
I can still do that.. but wanted to be sure of that you have given every possible detail(s)

Regards,
DILIPandey

<click on below 'star' if this helps>

In E2 and copy down,

=IF(A2=A3, "", TEXT(INDEX(B:B, MATCH(A2, A$1:A2, 0)), "hh:mm - ") & TEXT(C2, "hh:mm"))

That was it!

I'm sorry for the late reply but I don't work on sunday so I didn't had access to the project to try it out...

However i still have one little problem... if a task is repeated it shows the 1st value in the "starting time" instead of the new one... is there a way to make this happen without it repeating the values in starting time?

Starting & End time of task.xlsx the attach has the ones i am talking about highlighted!

Thanks again and sorry for all the trouble

Try

=IF(A2=A3, "", TEXT(LOOKUP(2, 1/((A$1:A1<>A2)*(A$2:A2=A2)), B$2:B2), "hh:mm - ") & TEXT(C2, "hh:mm"))