Rounding number up.

[noveske]

F5: Input. Actual input: 14.78 (m)
O5: 14.7
B14: Current time. (1948)
C14: 0014

C14 will display the number of minutes, until B14 receives a time. Once B14 and C14 have values, they will add to the time.

Formulas in O5 and C14.

O5.

=LEFT(F5,SUM(LEN(F5)-LEN(SUBSTITUTE(F5,{"0","1","2","3","4","5","6","7","8","9"},""))))

C14.

=IFERROR(B14+TIME(,O5,0),0)

I need it to round up to a whole number at some point. Any decimal is to round up to the next whole number. Always. Even .01.
Can be anywhere. Just need C14 to display 0015, since that's what's being added.

 

[awoohaw]

okay, I think that helps.
regarding your entry into cell B14. when you enter v=8/31/2023 7:48:46 PM but format it as hh:mm;@ I am pretty sure you are not truncating/rounding the 46 seconds out. Whether it is .78 or .7 of 60 seconds is a difference of a bit more than 4 seconds subtracted.

While I understand you want to use number in F5 and O5, I think by mixing different value types can cause confusing and errors, If you get it right, great, but anyone coming behind you with this will have problems trying to figure it out.

 

[noveske]

okay, I think that helps.
regarding your entry into cell B14. when you enter v=8/31/2023 7:48:46 PM but format it as hh:mm;@ I am pretty sure you are not truncating/rounding the 46 seconds out. Whether it is .78 or .7 of 60 seconds is a difference of a bit more than 4 seconds subtracted.

While I understand you want to use number in F5 and O5, I think by mixing different value types can cause confusing and errors, If you get it right, great, but anyone coming behind you with this will have problems trying to figure it out.

I'm understanding what you're saying.
In the beginning it was just getting it to work.
I think part of it is just forcing the format.
Looking at it now, I could have just went by time.

Thanks for looking at it and explaining it to me. Helped me understand and learn.

 

[awoohaw]

your welcome. But, I'm really curious as to why to roundup/apply a ceiling to B5 to then add to B14. Can you tell me why you do that?
I know you want C14 to be a rounded number.

 

[noveske]

When I first made the spreadsheet, I think I was just trying to figure it out.
Duration and start time was manually entered. I have to manually select and copy each row.

So at that time, it was the only way I could figured out how to make it do what I wanted. Didn't know all the capabilities. So problem solving went the long route.
I think initially I did it this way because of the (m) stuck to the minutes.
Source formatting was junked, which I have no control over.
Then it was the only way I got it to work. So no reasoning.

C14 always displayed as a rounded number.
It was just adding a minute to the time. Since for duration reporting, I'd say 15 minutes and not 14.78.

I guess instead of F5 going to O5 to C14, then adding B14...



So at first this worked. But when I cleared the sheet, it broke. Womp womp.

I could just drop this in C14...

=B14 + TIME(0, CEILING(F5, 1), 0)

How would you recommend doing it?

 

[awoohaw]

That formula worked for me:
This does not use cell O5 at all.

Mr excel questions 58.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3
4						minutes?
5						14.78	GENERAL FORMAt								14.7	GENERAL
6
11
12
13
14	hhmm;@	1948	2003
noveske

Cell Formulas
Range	Formula
O5	O5	=LEFT(F5,SUM(LEN(F5)-LEN(SUBSTITUTE(F5,{"0","1","2","3","4","5","6","7","8","9"},""))))
C14	C14	=B14 + TIME(0, CEILING(F5, 1), 0)

There is also a way to deal with non decimal (10/100/1000) fractions using the DOLLARFR() function (Dollar Fractional). You could look into that.

Mr excel questions 58.xlsm
	C	D
15
16	14.468
17
noveske

Cell Formulas
Range	Formula
C16	C16	=DOLLARFR(14.78,60)

 

[noveske]

That formula worked for me:
This does not use cell O5 at all.

Mr excel questions 58.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3
4						minutes?
5						14.78	GENERAL FORMAt								14.7	GENERAL
6
11
12
13
14	hhmm;@	1948	2003
noveske

Cell Formulas
Range	Formula
O5	O5	=LEFT(F5,SUM(LEN(F5)-LEN(SUBSTITUTE(F5,{"0","1","2","3","4","5","6","7","8","9"},""))))
C14	C14	=B14 + TIME(0, CEILING(F5, 1), 0)

There is also a way to deal with non decimal (10/100/1000) fractions using the DOLLARFR() function (Dollar Fractional). You could look into that.

Mr excel questions 58.xlsm
	C	D
15
16	14.468
17
noveske

Cell Formulas
Range	Formula
C16	C16	=DOLLARFR(14.78,60)

No idea. I'm getting #VALUE!.

Maybe it's the (m)?

 

[awoohaw]

No idea. I'm getting #VALUE!.

Maybe it's the (m)?

I took the (m) out of cell F5. If you really need it then you'll need to do a text conversion something like:

=B14 + TIME(0, CEILING(TEXTBEFORE(F5," (m)"), 1), 0)

Mr excel questions 58.xlsm
	A	B	C	D	E	F	G
1
2
3
4						minutes?
5						14.78 (m)	GENERAL FORMAt
6
11
12
13
14	hhmm;@	1948	2003
15
16			14.468
noveske

Cell Formulas
Range	Formula
C14	C14	=B14 + TIME(0, CEILING(TEXTBEFORE(F5," (m)"), 1), 0)
C16	C16	=DOLLARFR(14.78,60)