Time Formula

[kevinb11]

Need a Formula to figure the difference between time then round to nearest 15 minutes and change to number with a decimal point. Can it be done in one step or does it need to be multiple steps?

Tried =MOD(MROUND((B29-B28),"0:15"),1)
But I keep getting NUM! error

Thanks for any help you can provide

 

[6StringJazzer]

I am not getting a NUM error using the same data and same formula. Are your time values really time values, or are they text strings? The data is left-justified which suggests they are text.

Also in your sample data it looks like B29 is smaller than B28, unless you are omitting AM/PM from your formatting.

When you say you want a number with a decimal point, do you want this in units of hours?

It's always better to provide actual data vs. pictures.

In any case here is what I get with your data:

$scratch.xlsm
	B	C	D	E
28	8:00 AM		Time	Hours
29	1:00 PM		5:00	5.00
Sprint

Cell Formulas
Range	Formula
D29	D29	=MOD(MROUND((B29-B28),"0:15"),1)
E29	E29	=D29*24

And here is a similar example to show the rounding working:

$scratch.xlsm
	B	C	D	E
28	8:00 AM		Time	Hours
29	1:13 PM		5:15	5.25
Sprint

Cell Formulas
Range	Formula
D29	D29	=MOD(MROUND((B29-B28),"0:15"),1)
E29	E29	=D29*24

 

[Dave Patton]

Date and Time 2021.xlsm
	A	B	C	D	E
1
2		08:00			Hours
3		13:12			5.25
4
5		08:00
6		13:23			5.5
7
8		5:00 AM	8:25 PM	15.50
9		8:00 AM	1:12 PM	5.25
10		8:00 AM	1:23 PM	5.50
13b

Cell Formulas
Range	Formula
E3,E6	E3	=MROUND((B3-B2)*24,0.25)
D8:D10	D8	=MROUND((C8-B8)*24,0.25)

 

[kevinb11]

I am not getting a NUM error using the same data and same formula. Are your time values really time values, or are they text strings? The data is left-justified which suggests they are text.

Also in your sample data it looks like B29 is smaller than B28, unless you are omitting AM/PM from your formatting.

When you say you want a number with a decimal point, do you want this in units of hours?

It's always better to provide actual data vs. pictures.

In any case here is what I get with your data:

$scratch.xlsm
	B	C	D	E
28	8:00 AM		Time	Hours
29	1:00 PM		5:00	5.00
Sprint

Cell Formulas
Range	Formula
D29	D29	=MOD(MROUND((B29-B28),"0:15"),1)
E29	E29	=D29*24

And here is a similar example to show the rounding working:

$scratch.xlsm
	B	C	D	E
28	8:00 AM		Time	Hours
29	1:13 PM		5:15	5.25
Sprint

Cell Formulas
Range	Formula
D29	D29	=MOD(MROUND((B29-B28),"0:15"),1)
E29	E29	=D29*24

They are set up as military time.

 

[6StringJazzer]

They are set up as military time.

Well then 1:00 - 8:00 is a negative number. What you are trying to figure out?

 

[Dave Patton]

Did you check post #3?
N.B. You can copy the post to a clean sheet. See the icon below the f(x) in the heading.

Date and Time 2021.xlsm
	A	B	C	D	E
1
2		08:00			Hours
3		01:00			7
4
5		13:23
6		08:00			5.5
7
8		Start	End
9		1:00	8:00	7.00
10		8:00	13:12	5.25
11		8:00	13:23	5.50
12
13b

Cell Formulas
Range	Formula
E3,E6	E3	=MROUND((B2-B3)*24,0.25)
D9:D11	D9	=MROUND((C9-B9)*24,0.25)

 

[kevinb11]

Did you check post #3?
N.B. You can copy the post to a clean sheet. See the icon below the f(x) in the heading.

Date and Time 2021.xlsm
	A	B	C	D	E
1
2		08:00			Hours
3		01:00			7
4
5		13:23
6		08:00			5.5
7
8		Start	End
9		1:00	8:00	7.00
10		8:00	13:12	5.25
11		8:00	13:23	5.50
12
13b

Cell Formulas
Range	Formula
E3,E6	E3	=MROUND((B2-B3)*24,0.25)
D9:D11	D9	=MROUND((C9-B9)*24,0.25)

Formula works good since I changed time format to 1:00 PM, but throws a NUM! error when the last goes past midnight.

Sorry cannot upload a mini sheet due to using a company computer. I don't have permission. Thanks!

 

[Dave Patton]

A few examples with expected results would have helped.

Date and Time 2021.xlsm
	B	C	D
8	Start	End
9	1:00	8:00	7.00
10	8:00	13:12	5.25
11	8:00	13:23	5.50
12	10:00	02:00	16.00
13	22:00	02:00	4.00
14	22:53	03:00	4.00
15	22:31	04:00	5.50
13b

Cell Formulas
Range	Formula
D9:D15	D9	=MROUND(MOD(C9-B9,1)*24,0.25)

 

[kevinb11]

A few examples with expected results would have helped.

Date and Time 2021.xlsm
	B	C	D
8	Start	End
9	1:00	8:00	7.00
10	8:00	13:12	5.25
11	8:00	13:23	5.50
12	10:00	02:00	16.00
13	22:00	02:00	4.00
14	22:53	03:00	4.00
15	22:31	04:00	5.50
13b

Cell Formulas
Range	Formula
D9:D15	D9	=MROUND(MOD(C9-B9,1)*24,0.25)

Perfect. Thank you so much

 

[Dave Patton]

Thanks for the feedback.