Copying A1 to A20 if the time is greater than

[Sumeluar]

Hello all

I have on A1 Machine name, Start Time and End Time on cells A2 and A3 respectively and on A4 I have Total Hours. I want to separate all the down time in two categories, as follows: If the diference in time between A2 &A3 is greater than 59 minutes A1 should automatically get copied on A20, if the same is greater than 279 minutes it should go to B20.

Maybe is easy, Does anybody knows how?

Kind Regards!

Sumeluar

 

[Barry Katcher]

How about this:

Book1
	A	B	C	D
1	Machine1	2:00 PM	6:40 PM	280
2				Formula above is:
3				=(C1-B1)*1441
4		Machine1
5	Formula above is:	Formula above is:
6	=IF(AND(D1>59,D1<280),A1,"")	=IF(D1>279,A1,"")
Sheet1

 

[Sumeluar]

Barry Katcher, thanks for your reply, the formula works good except for tecnicians that start their shift , let's say 3:30 PM and the equipment breakdown carries past midnight, then it doesn't work, Any ideas?

Regards!

Sumeluar

 

[Barry Katcher]

O.K., new formula.

Formula in D1 is: =(C1-B1+(C1<B1))*1440
Format the cell as "General"

Book1
	A	B	C	D
1	Machine1	11:00 PM	12:20 AM	80
2
3
4	Machine1
5	Formula above is:	Formula above is:
6	=IF(AND(D1>59,D1<280),A1,"")	=IF(D1>279,A1,"")
Sheet1

 

[Sumeluar]

Great job Barry Katcher, that's exactly what I wanted. The mistery has been solved. Until next time. Thanks again.
Kind regards!

Sumeluar