Simple question, easy answer sought. I've been out of the game for a while.

[Viper1965]

Hi,

Say I have an amount of time left to work as 6.45, in standart decimal.

I am looking for a simple expression that would display this like the following: 6 Hours and 45 Minutes left to work.

I found several functions that parcel the fraction out, but I cannot seem to combine it into one formula. I want no leading zeros on the decimal part. Just simply tell me the hours and minutes of work remaining.

I apologize for the stupid question, but I've been out of the excel vba or formula game for quite a while. (and I'm old!)

Can you savvy excel programmers help me out?

Thanks,

Viper

PS, the numbers .45 and 45 minutes are coincidental. I am just making numbers up.

 

[bosco_yip]

Up load a file to us, saying what you have and what you want?

Giving around 10 rows data.

 

[vletm]

Viper1965​

Could You use 6:45 instead of 6.45?
... then You could calculate times as those are ... times.

 

[GraH - Guido]

Maybe =Time(int(Timeval), mod(Timeval;1)*100;0)

 

[pecoflyer]

HI. Funny but useless title

 

[polarisking]

Assuming time expressed as 6.45 is in cell A1.

=INT(A1)&" Hours and "&MOD(A1,1)*100&" Minutes left to work"

 

[BobBridges]

Assuming "6.45" means "six point four five hours", and not "six hours and forty-five minutes" (which I infer from the fact that you took the trouble to say that "45" and ".45" were just coincidental), then maybe the simplest way is to divide 6.45 by 24, which gives you 0.26875 days. Just display that directly using the custom format "[h]:mm", which comes out as 6:27, ie six hours and 27 minutes.

 

[Viper1965]

Hi, didn't want to not get back to you guys.

I started out using the formula given by polarisking. Didn't quite work right, so I fixed it.

This is the exact formula that I am now using:
=(INT($AH$52)&" Hours and "&INT(((MOD($AH$52,1)*60)/100)*100) &" Minutes left to work!")

Although it doesn't look "right" to me, it works.

This is the output as of right now:

10 Hours and 37 Minutes left to work!

If anyone can see any error in that, or a way to simplify it, let me know, but as far as I'm concerned, problem solved. Although I like more elegant formulae, this worked for me and I didn't have to spend hours (which I don't have) coming up with something else.

Thanks to all who participated.