Calculate time in 6 minute increments

Thanks! It work!

Do you have any interest in explaining how this works? I'm very interested.

=0.1*INT(  (5+  (MOD(B2-B1,6)=0)  +  TEXT(B2-B1,"[m]") )  /6  )

First off, as I am sure you guessed, this...

TEXT(B2-B1,"[m]")

returns the number of minutes (the square brackets around the "m" allows the function to return a value greater than 60 if the number of minutes exceeds 60). Next, in order to get the range of values you want, we have to add 6 to this value (in anticipation of a later calculation) which is what my original formula did; however, because you did not want the boundary points (6, 12, etc.) to bump up to the next interval, we have to subtract 1 from the 6 whenever the number of minutes is a boundary point... I do that with a logical expression, namely, this one...

MOD(B2-B1,6)=0

Logical expressions evaluate to either TRUE or FALSE and, when involved in a mathematical expression, these get converted to 1 or 0 respectively. The MOD function above will return TRUE if B2-B1 is a multiple of 6 and FALSE otherwise. Multiples of 6 are your boundary points that you do not want bumped up. So, that constant value 5 will become a 6 whenever the number of minutes is NOT a multiple of 6 and remain 5 when it is. Next, we divide by 6 and apply the INT function inorder to reduce the calculated values to the numbers 1, 2, 3, etc. Note that the division by 6 is the reason we had to adjust the original constant 6 to a 5 when the number of minutes is a boundary point... boundary points divided by 6 are whole numbers which are one greater than the whole number parts of the values before the boundary points... the INT function chops off the decimal part and leaves the whole number... the whole number for boundary points is one greater than the whole number for values immediately prior to it... this meant

 that without making the above described adjustment, boundary points would be in the next interval from the values immediately before them (and you said you did not want that). Finally, the multiplication by 0.1 just adjusts the calculated values of 1, 2, 3, etc. to the range you wanted, namely, 0.1, 0.2, 0.3, etc.

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

ryzerman wrote:

I need the 6 minutes or less to be .1 (not .2) and 12 minutes or less to be .2 (not .3), etc.

=CEILING((B2-A2)*1440,6)/60

And it is!  Try typing 0:6:0 into column C instead of computing B-A.  I think you will see it does what you expect.

I suspect the problem is:  the times 2:00 and/or 2:06 are not exactly that, but have fractional digits representing seconds, either intentionally or arithmetic artifacts.

Try:

=CEILING(INT(C3*1440),6)/6 * 0.1

PS:  I use INT instead of ROUND(C3*1440,0) because Excel truncates, not rounds, time to minutes when using the h:mm format.  When I wrote CEILING(C3*1440,6) originally, I had assumed you would want 0:6:59 to round up to 12 minutes.

Thanks as well! This is also really close. It does what I posted above.

I need the 6 minutes or less to be .1 (not .2) and 12 minutes or less to be .2 (not .3), etc.

=CEILING((B2-A2)*1440,6)/60

 

Start (A)	Finish (B)	Diff (C)	Time (D)
2:00 PM	2:00 PM	0:00	0.0
2:00 PM	2:01 PM	0:01	0.1
2:00 PM	2:02 PM	0:02	0.1
2:00 PM	2:03 PM	0:03	0.1
2:00 PM	2:04 PM	0:04	0.1
2:00 PM	2:05 PM	0:05	0.1
2:00 PM	2:06 PM	0:06	0.2
2:00 PM	2:07 PM	0:07	0.2

 

Thanks for your help!

Hello again,

That's weird. Next guess is:

=FLOOR((B2-B1)*1440,6)/60+0.1*(MOD(B2-B1,1)<>0)

but what do you expect for 2:00pm - 2:54pm and what for 2:00pm - 2:55pm?

Regards,

Bernd

http://www.sulprobil.com/html/excel_forums.html

ryzerman wrote:

I'm trying to log time in 6 Minute increments. If work is 6 minutes or less = .1, 12 minutes or less = .2,......60 minutes or less = 1.

[....]

Any ideas on how to create the above answer based on the result in B3?

Try:

=CEILING(B3*1440,6)*0.1

=CEILING((B2-B1)*1440,6)/60

Format as number.

Regards,

Bernd

http://www.sulprobil.com/html/excel_forums.html