Calculating specific time for tasks

[Freeworld]

I am trying to Calculate time on a specific tasks per employee for scheduling purpose.

I have 2 categories.

LOBBY = 45MIN
OFFICE = 45MIN

looking for if cell = lobby than calculate time as 45mins and add for total time on lobby.

If I am able to send a screen shot of the work file I can show for better understanding.

 

[admiral100]

Hi,

Try this : Ctrl+Shift+Enter NOT just Enter

C1 =TEXT(SUM(IF(A1:A3="LOBBY",SUBSTITUTE(B1:B3,"MIN","")/1440)),"HH:MM")

	A	B	C
1	LOBBY	45MIN	01:05
2	OFFICE	45MIN
3	LOBBY	20MIN

<colgroup><col width="70" span="4" style="width:52pt"> </colgroup><tbody>
</tbody>

 

[Freeworld]

Table looks more like this.


Time. 9:00am|9:45am|10:00am
Employee name
Employee 1. Lobby. Office. Lobby
Employee 2. Office. Lobby. Office.
Employee 3. Office. Office. Lobby

 

[admiral100]

Try this :

G3 =SUMPRODUCT(--(B3:F3="Lobby"),$C$1:$G$1-$B$1:$F$1)

	A	B	C	D	E	F	G
1	Time	9:00 AM	9:45 AM	10:00 AM	10:45 AM	11:30 AM
2	Employee name
3	Employee 1	Lobby	Office	Lobby	Lobby	Office	02:15
4	Employee 2	Office	Lobby	Office	Lobby	Office	01:00
5	Employee 3	Office	Office	Lobby	Office	Office	00:45

<colgroup><col><col><col span="2"><col span="3"><col></colgroup><tbody>
</tbody>

 

[Freeworld]

Thank you, worked like a charm