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]

what does the "(m)" mean? Are you saying this is 14.78 minutes?
What does "(1948)" mean? Do you mean 1948 minutes or 19:48:00 as a time?
One thing I think, is that you are mixing time display with time values.
1948 as a date time value is really May 1, 1905 12:00 AM.

Mr. Excel has a tool, xl2bb that allows you to post mini workbooks of your data. I don't think the forum has enough information to answer your question as we need to see how you have formatted all of the cells.

 

[noveske]

what does the "(m)" mean? Are you saying this is 14.78 minutes?
What does "(1948)" mean? Do you mean 1948 minutes or 19:48:00 as a time?
One thing I think, is that you are mixing time display with time values.
1948 as a date time value is really May 1, 1905 12:00 AM.

Mr. Excel has a tool, xl2bb that allows you to post mini workbooks of your data. I don't think the forum has enough information to answer your question as we need to see how you have formatted all of the cells.

(m) is minutes
1948 is time

Can't use xl2bb. Work comps have restrictions.

Data that's pasted to F5 is "15 (m)"
Clicking the Time button in B13 will enter the current time into B14.

C14 will add F5 to the B14 time.

It's the minutes that needs rounded up as they are random durations. So all minute values x.01 and up should be rounded to the next whole number.
I'm able to get F5 to round up by formatting, but that doesn't round up the actual value to add to time.

 

[awoohaw]

what time is 1948.? 18 min 48 seconds the 1,948 minute of the day? Please use COLONS when writing a time in text.

 

[noveske]

what time is 1948.? 18 min 48 seconds the 1,948 minute of the day? Please use COLONS when writing a time in text.

Sorry about that. Just so used to 24-hour time.

19:48:00 (hh:mm:ss)
7:48 pm

 

[awoohaw]

so, getting back to your values. I don't think you really cleared up the questions, although I can guess.
Are any cells in your worksheet formatted as TIME, or are they just numbers.
for instance the value 1948 is May 1, 1905, not 7:48 PM. Understanding how you input values is ULTRA critical or you may get wrong answers and the forum will be wasting their time making guesses as to what you want.
Is F5 a number of minutes and not a time?
Is B14 and number and not a time?
How is O5 formatted? Is it a time format or a general format? I am thinking this should be a number of minutes and not a time because you use this in the TIME() conversion function second argument.
Is C14 supposed to be a time result (Like "19:30 PM")?

 

[noveske]

F5 - General
B14 - Custom (hhmm;@)
O5 - General
C14 - Format same as B14.

 

[awoohaw]

F5 - General
B14 - Custom (hhmm;@)
O5 - General
C14 - Format same as B14.

in your first post you write this:

F5: Input. Actual input: 14.78 (m) >>>>> as general this displays as 14.78 (m) >>> this will be treated as text
and therefore the formula in O5 will read the text in F5 and resolve to 14.7
B14: Current time. (1948) >>>> When I format the cell as hhmm;@ this gets formatted as 0000, and the value in the formula bar shows May 5, 1905 00:00:00 >>> so I am confused how it works for you.
O5: 14.7 this is fine as general but it is text you are trying to add as an number of minutes in C14 >>>>TIME(0,14.7,0)... >>> Is this supposed to be 14+70/100 minutes? which is aprox 14minutes and 46 seconds.
C14: 0014 Im not sure how you reasonable are doing your time addition and formats.

But, I'm guessing that you are mostly looking for a way to add the number of minutes in O5 to the time value in B14 and display this in cell C14.

when you mention rounding the 14.78 to 14.7, what do you mean by whole number ... a whole minute or a whole second?

this is what I have, according to your instructions. You can copy and paste this into one of your worksheets.
Below this is an image showing you how the value of the cell with 1948 entered, but formatted as hhmm;@ appears in the formula bar.

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 (m)	GENERAL FORMAt								14.7	GENERAL
6
11
12
13		Data in B14: 1948		text in col C or the formula
14	hhmm;@	0000	0014	=B14+TIME(0,O5,0)
15	general:>>>>>>		1948.01	1948
16	yyyy/mm/dd hh:mm:ss >>>>>		1905/05/01 00:14:00	=B14+TIME(0,O5,0)
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"},""))))
B13	B13	="Data in B14: " & TEXT(B14,0)
C14	C14	=B14+TIME(0,O5,0)
D14,D16	D14	=FORMULATEXT(C14)
C15	C15	=B14+TIME(0,O5,0)
D15	D15	=TEXT(C15,0)
C16	C16	=B14+TIME(0,O5,0)

So, i'll work with the format you desire but I really need to know what are the meanings of the values your are entering in cells F5 and B14, and what is the desired result of cells O5 >> is a a value of time as on a clock or a number of minutes.
And for cell B14 in a date/time scenario of any format the value of 1948 is really the 1948th day since 12/31/1899 or May 1, 1905.
SO AGAIN please tell me what is it you are trying to input in the cells.

 

[noveske]

Sorry about that. Somehow I got most of it to work a while back. Just fine tuning now. So I had to look through it again. Didn't think to get all the details.
I think I got it now.

F5 - Duration in minutes. The actual input is "14.78 (m)". Formula strips off the (m). .78 of a minute. So 14 minutes and 47 seconds.

B14 - Actual input "8/31/2023 7:48:46 PM". Format: Custom - hhmm;@. Display as 1948.

O5 - Is a formula. When F5 has a value it is displayed here. Format: General. Display as 14.7.

C14 - Is a formula. When B14 and F5 have values, then it takes the time and adds the minutes. Formatting custom - hhmm;@. Displays 2002.

What I've been trying to do it have O5 round up to 15.
C14 should result 2003.

Currently all minutes are being round down.
I'm looking to round them all up.

So 14.78 should be made whole by being 15. Anytime seconds are present, the minute needs to round up to the next whole number.

So 1.01 to 2.
.20 to 1...

 

[noveske]

So, i'll work with the format you desire but I really need to know what are the meanings of the values your are entering in cells F5 and B14, and what is the desired result of cells O5 >> is a a value of time as on a clock or a number of minutes.
And for cell B14 in a date/time scenario of any format the value of 1948 is really the 1948th day since 12/31/1899 or May 1, 1905.
SO AGAIN please tell me what is it you are trying to input in the cells.

Sorry to be such a pain. I think I figured it out.

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

This just rounds values up.