make a chnge

Hi,

I have

In E4 then copy down

Please Login or Register  to view this content.

It appears that the "expected results" column is simply a count of the number of 30 second increments that have elapsed. Sometimes the difficult part of this sort of thing is defining the exact boundary where the result shifts from 0 to 1 to 2 to 3 etc. Your written description, for example, led me to believe that exactly 30 seconds would be a 1, exactly 1 minute would be 2, 1:30 would be 3, and so on, but your "expected results" column shows a 1 next to 1:00, and a 2 next to 1:30, and so on. For now, I'll assume that the "expected results" column is correct.

Modifications to the existing formula in column E:

1) I notice that the formula in column D strips all seconds information from column C. Since your desired result depends on the seconds information, the first change is to reference column C rather than column D =ROUND(MOD(C4,1)*60*24,0)
2) The current formula is set up to use time in minutes since midnight, and the goal of the formula is to get the number of 0.5 minute increments that have passed, so we divide the time in minutes by 0.5 to get the number of half minute increments =ROUND(MOD(C4,1)*60*24/0.5,0)
3) Now we need to round that result down to the nearest integer, so replace the ROUND() function with the ROUNDDOWN() function. =ROUNDDOWN(MOD(C4,1)*60*24/0.5,0) This would work just fine if you want 0.5 minutes to be 1 and 1.0 minutes to be 2.
4) Since your expected results shows 1.0 to be 1 and 1.5 to be 2 and so on, we need to subtract a small amount from the result before rounding down to properly account for the boundary value. Since the raw data are given to the nearest 0.01 seconds (0.00017 minutes), I choose to subtract about half of that value =ROUNDDOWN(MOD(C4,1)*60*24/0.5-0.00008,0)

That should replicate your expected results column.