Calculating time based on delivered output

[richiebkerry]

Hello, I need to calculate time based on a measured output in a table. Basically I'm try to generate a standard amount of time per output.

The rules are:

0 to 1 is 20 minutes flat (even if 0.1 is delivered or 0.9 it's still 20 minutes) and every unit after that is 10 minutes per unit (if 2.3 it needs to be 20 minutes for the first 1.0 + 10 minutes x 1.3 pro rata'd) however, the maximum time can never breach 60 minutes.

Thanks in advance.

 

[jmacleary]

Hi there. Assuming your data is in A1, this should do it: =MIN(20+(MAX(A1-1,0)*10),60)

 

[richiebkerry]

Thankyou Sir, greatly appreciated

 

[richiebkerry]

Thankyou jmacleary, it gives a number. How do I get it in a format so that 46 converts to 0:46:00?