EXCEL Forumulas please

  • Status: Closed
  • Prize: $20
  • Entries Received: 4
  • 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

Recommended Skills

Employer Feedback

“Very good thanks”

Profile image uksikh, United Kingdom.

Top entries from this contest

View More Entries

Public Clarification Board

  • imfarrukh47
    imfarrukh47
    • 1 month ago

    the contest is open. Do you still accept the entries

    • 1 month ago
  • sakhawat2
    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. uksikh
      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

    Post Your Contest Quick and easy

  • Get tons of entries

    Get Tons of Entries From around the world

  • Award the best entry

    Award the best entry Download the files - Easy!

Post a Contest Now or Join us Today!