Rounding UP to the nearest 6 minutes - The difference between two times

[gchopperp]

Hi all.
I use Excel for aviation time calculations and we work our times in 6 minute slops. For example 08:00-08:30 equals 0,5 whilst 08:00 to 08:32 equals 0,6.
I cant seem to find a formula that calculates this difference correctly and displays it correctly.

10:00	10:30	0,50
11:00	11:30	0,60
12:00	12:30	0,60
13:00	13:30	0,60
14:00	14:30	0,50

the formula I've come up with that works some of the time is =(CEILING((I13-H13);"00:06"))*(1440)/60 first column is I and second column is H.

HELP!!

 

[6StringJazzer]

Is that an example of what you want, or an example of wrong results? Because it looks like they should all be 0,50.

This formula should give you want you want.

=CEILING.MATH((I3-H3)*24*60;6)/60

Examples: (U.S. style format)

$scratch.xlsm
	A	B	C
1	10:00	10:03	0.1
2	10:00	10:06	0.1
3	10:00	10:09	0.2
4	10:00	10:12	0.2
5	10:00	10:15	0.3
6	10:00	10:18	0.3
7	10:00	10:21	0.4
8	10:00	10:24	0.4
9	10:00	10:27	0.5
10	10:00	10:30	0.5
Round to 6 mins

Cell Formulas
Range	Formula
B1	B1	=A1+"00:03:00"
C1:C10	C1	=CEILING.MATH((B1-A1)*24*60,6)/60
B2:B10	B2	=B1+"00:03:00"

 

[ExceLoki]

Book1
	A	B	C
1	10:00	10:10	0.20
2	11:00	11:20	0.40
3	12:00	12:18	0.30
4	13:00	13:31	0.60
5	14:00	14:30	0.50
6	8:00	8:32	0.60
7	8:00	8:06	0.10
Sheet1

Cell Formulas
Range	Formula
C1:C7	C1	=CEILING(ROUND((B1-A1)*1440/60,6),0.1)

 

[gchopperp]

Is that an example of what you want, or an example of wrong results? Because it looks like they should all be 0,50.

This formula should give you want you want.

=CEILING.MATH((I3-H3)*24*60;6)/60

Works well except 11:00- 11:30 still records as 0,6 not 0,5.  Other solution worked.  Thanks 6String

[/QUOTE]

 

[gchopperp]

Worked 100%. Thanks Loki

 

[6StringJazzer]

Works well except 11:00- 11:30 still records as 0,6 not 0,5. Other solution worked. Thanks 6String

Works for me.

$scratch.xlsm
	A	B	C
10	11:00	11:30	0.5
Round to 6 mins

Cell Formulas
Range	Formula
C10	C10	=CEILING.MATH((B10-A10)*24*60,6)/60

 

[ExceLoki]

happy to help

 

[MARK858]

Works for me.

I get 0.6 as well

Book1
	A	B	C
10	11:00:00	11:30:00	0.6
Sheet3

Cell Formulas
Range	Formula
C10	C10	=CEILING.MATH((B10-A10)*24*60,6)/60

 

[MARK858]

@6StringJazzer Evaluation steps if interested

 

[ExceLoki]

that's why i added in the round to 6th digit (could've prolly been 2 or 3 honestly...)