• Status: Closed
• Prize: \$20
• Winner: bhavdipporiya

## Contest Brief

We previously developed the below formula which produced the banded decimals in the final column below (also attached):

=IF(G3="","",IF(TIME(IFERROR(MID(G3,1,SEARCH(" hour",G3)-1),0),IFERROR(MID(G3,IF(ISERROR(SEARCH(" minute",G3)),0,IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0)+1),IFERROR(SEARCH(" minute",G3)-1-IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0),0)),0),0)*1440=0,0,IF(TIME(IFERROR(MID(G3,1,SEARCH(" hour",G3)-1),0),IFERROR(MID(G3,IF(ISERROR(SEARCH(" minute",G3)),0,IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0)+1),IFERROR(SEARCH(" minute",G3)-1-IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0),0)),0),0)*1440<=17,0.25,IF(TIME(IFERROR(MID(G3,1,SEARCH(" hour",G3)-1),0),IFERROR(MID(G3,IF(ISERROR(SEARCH(" minute",G3)),0,IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0)+1),IFERROR(SEARCH(" minute",G3)-1-IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0),0)),0),0)*1440<=22,0.33,IF(TIME(IFERROR(MID(G3,1,SEARCH(" hour",G3)-1),0),IFERROR(MID(G3,IF(ISERROR(SEARCH(" minute",G3)),0,IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0)+1),IFERROR(SEARCH(" minute",G3)-1-IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0),0)),0),0)*1440<=35,0.5,IF(TIME(IFERROR(MID(G3,1,SEARCH(" hour",G3)-1),0),IFERROR(MID(G3,IF(ISERROR(SEARCH(" minute",G3)),0,IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0)+1),IFERROR(SEARCH(" minute",G3)-1-IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0),0)),0),0)*1440<=50,0.75,IF(TIME(IFERROR(MID(G3,1,SEARCH(" hour",G3)-1),0),IFERROR(MID(G3,IF(ISERROR(SEARCH(" minute",G3)),0,IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0)+1),IFERROR(SEARCH(" minute",G3)-1-IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0),0)),0),0)*1440<=65,1,IF(TIME(IFERROR(MID(G3,1,SEARCH(" hour",G3)-1),0),IFERROR(MID(G3,IF(ISERROR(SEARCH(" minute",G3)),0,IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0)+1),IFERROR(SEARCH(" minute",G3)-1-IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0),0)),0),0)*1440>65,TIME(IFERROR(MID(G3,1,SEARCH(" hour",G3)-1),0),IFERROR(MID(G3,IF(ISERROR(SEARCH(" minute",G3)),0,IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0)+1),IFERROR(SEARCH(" minute",G3)-1-IFERROR(SEARCH(" hour",G3)+IF(ISERROR(SEARCH(" hours",G3)),5,6),0),0)),0),0)*24))))))))

28 minutes 0.5
41 minutes 0.75
53 minutes 1
22 minutes 0.33

We would now like the formula to produce the following banded decimals.

0 to 22 minutes – 0.25

23 – 37 minutes - 0.5

38 – 52 minutes - 0.75

53 to 67 minutes - 1

above 67 minutes - no amendment - just convert the minutes to decimals (e.g. 90 minutes would be 1.5)

ALSO.

We would like another formula creating which produces time in decimels but rounded up to the nearest 5 minutes. Example:

1 hour = 1 hour = 1
30 mins = 30 mins = 0.5
32 mins = FORMULA ROUNDS UP TO 35 mins (nearest 5 mins) = 0.583
31 mins = FORMULA ROUNDS UP TO 35 mins (nearest 5 mins) = 0.583
44 mins = FORMULA ROUNDS UP TO 45 mins (nearest 5 mins) = 0.750

Thanks

## Employer Feedback

“Very good thanks” uksikh, United Kingdom.

## Public Clarification Board

• ###### imfarrukh47
• 1 month ago

the contest is open. Do you still accept the entries

• 1 month ago
• ###### sakhawat2
• 1 month ago

Hi,

Would you be ok with a VBA solution instead of formulas? VBA will make the calculation much-much simpler and effective.

Thank you.

• 1 month ago
1. Contest Holder
• 1 month ago

no thanks - this needs to be per the below and editable in a excel sheet and similar to the above formula

• 1 month ago

## How to get started with contests

• Post Your Contest Quick and easy

• Get Tons of Entries From around the world

• 