Copying A1 to A20 if the time is greater than

Sumeluar

Board Regular
Joined
Jun 21, 2006
Messages
245
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
How about this:
Book1
ABCD
1Machine12:00 PM6:40 PM280
2Formula above is:
3=(C1-B1)*1441
4 Machine1
5Formula above is:Formula above is:
6=IF(AND(D1>59,D1<280),A1,"")=IF(D1>279,A1,"")
Sheet1
 

Sumeluar

Board Regular
Joined
Jun 21, 2006
Messages
245
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

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
O.K., new formula.

Code:
Formula in D1 is: =(C1-B1+(C1<B1))*1440
Format the cell as "General"
Book1
ABCD
1Machine111:00 PM12:20 AM80
2
3
4Machine1 
5Formula above is:Formula above is:
6=IF(AND(D1>59,D1<280),A1,"")=IF(D1>279,A1,"")
Sheet1
 

Sumeluar

Board Regular
Joined
Jun 21, 2006
Messages
245
Great job Barry Katcher, that's exactly what I wanted. The mistery has been solved. Until next time. Thanks again.
Kind regards!

Sumeluar
 

Forum statistics

Threads
1,136,345
Messages
5,675,220
Members
419,553
Latest member
hanahass

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top