Hi

One way would be to use a helper cell and convert all time intervals into
decimal minutes. I used cell K1 and entered
=(K7-TIME(1,30,0))*24*60
Excel stores times as fractions of a day so multiplying by 24 gives hours,
and further multiplying by 60 gives minutes.

Then your time intervals after 1:30 are 15 mins, 25, 20, 10 or cumulatively
they are 15, 40, 60 and 70 and you are adding to the value in J7 either
2,4,6 or 10
so the formula can be simplified to

=(K1=0)*J7+(K115)*2+(K140)*2+(K160)*2+(K170)* 4

where the values within the brackets will either be True or False.

Multiplying them by a number, coerces them to either 1 for True or 0 for
False so if the calculated time in minutes is greater than or equal to 0,
then it must be at least 1:30 and we use the value in J7.

The rest of the formula then adds the incremental value, depending upon how
far past 1:30 we are.

Regards

Roger Govier


dwest100 wrote:
Is there a technique to shorten formulas such as this one?

=IF(K7=TIME(0,1,30)),J7,IF(AND(K7TIME(0,1,45),K7< =TIME(0,2,10)),J7+2,IF(AND(K7TIME(0,2,10),K7<=TIM E(0,2,30)),J7+4,IF(AND(K7TIME(0,2,30),K7<=TIME(0, 2,40)),J7+6,IF(K7TIME(0,2,40),J7+10))))))


I'm always creating these spaghetti style formulas and I'd like to
learn some techniques to shorten them.

Any advice or recommendations would be greatly appreciated!