Help with clock and time conversion and formula creation

[arnob216]

Hi

I want to make a sort of calculator in excel. The scenario is as follows:

Lets say I have 2 businesses and these have a certain stock level which gets converted to supply in certain times (hours and minutes only). What I want to make is to put Current clock time in 1 cell. Then depending on the "Stock to supply" conversion time, I want to know when I the stock will be fully converted. In more details:

Say, current time is 7 PM. My stock gets converted to supply in 2.5 hours (which means 2 hours and 30 minutes). How do I add this decimal number '2.5' to the 7 PM so that the result comes as 9:30 PM?
Here the 2.5 hours of conversion time will change depending on situations. Therefore, the figure could be any decimal number.

 

[HappierThan]

With your clock in A1 and metric time in B1:- =IF(B1="","",TIME(B1,MOD(B1*60,60),0))+A1

 

[FormR]

Hi, another option to try.

=A1+(B1/24)

Note, you may need to format the cell that contains the formula as time.

 

[arnob216]

Hi, another option to try.

=A1+(B1/24)

Note, you may need to format the cell that contains the formula as time.

But the result comes out as 2:30:00 instead of 9:30:00 for some reason.

 

[arnob216]

With your clock in A1 and metric time in B1:- =IF(B1="","",TIME(B1,MOD(B1*60,60),0))+A1

Thanks it worked!! Though I hardly understand the function

 

[arnob216]

Another thing I want to learn. How to convert 2.33 into 2 hours 20 mins or 2.5 into 2 hours 30 mins?

 

[FormR]

But the result comes out as 2:30:00 instead of 9:30:00 for some reason

Hi, here it is working for me.

Book9
	A	B	C
1	07:00	2.5	09:30
Sheet1

Cell Formulas
Range	Formula
C1	C1	=A1+(B1/24)

 

[FormR]

Another thing I want to learn. How to convert 2.33 into 2 hours 20 mins or 2.5 into 2 hours 30 mins?

Just divide the number by 24 and format as time.

 

[arnob216]

Hi, here it is working for me.

Book9
	A	B	C
1	07:00	2.5	09:30
Sheet1

Cell Formulas
Range	Formula
C1	C1	=A1+(B1/24)

Thanks working for me too. I had a mistake last time.

Just divide the number by 24 and format as time.

By doing this, I get the Time format. But I want to denote the figure before decimal as "x hours" and figure after decimal as "x minutes". Is that even possible?

 

[FormR]

I want to denote the figure before decimal as "x hours" and figure after decimal as "x minutes"

With your decimal number in B1 - you could try.

=TEXT(B1/24,"[H] ""hours"" m ""minutes""")