Converting Time into Number and Dividing in Excel

Hi All,

I am looking for a way to calculate time to 1 point for every 15 minutes. The remaining time should add 1 point if > 7 minutes and 0 points if < 7 minutes.

Example,
1. 0:50:00 <---convert into number format =HOUR(C7)+MINUTE(C7)/15
2. Will need to divide 50/15 = 3.33 after converting time into number format.

From the example above where it is 0:50 minutes, the child received only 3 points because there was an excess time of 5 minutes.
15*3 = 45 + (5 minutes extra) = 50 minutes. The 5 minutes extra is < 7 minutes, therefore the child received 0 points. If it was > 7 minutes the child would have received an additional point.

Is there a formula to configure time into the point system as indicated above?

Workbook: Time.xlsx

Any help is much appreciated.

Thank you,
Newb

In C5 Cell


=INT(TEXT(B5,"[m]")/15)


In D5 Cell


=IF(MOD(TEXT(B5,"[m]"),15)<7,0,1)


Drag both the formula's down...

...and another

C5

Formula:

Please Login or Register  to view this content.

D5

Formula:

Please Login or Register  to view this content.