J
Jorge
How do I convert 7.30 hours into 7.21 (ie 7 hours 21min.) Note I do not wish
to use the standard hour:minute formatting.
to use the standard hour:minute formatting.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Biff
Hi!
7.3 decimal = 7:18
Do you intend to use 7.21 as a numeric value in other calculations?
Biff
7.3 decimal = 7:18
Do you intend to use 7.21 as a numeric value in other calculations?
Biff
J
Jorge
Hi Biff,
My Mistake I meant to type 7.35. In answer to yur question, no I won't be
using any converted figure in any calculations. I've tried to create an alert
to signal when a set number of hours has been exceeded. I've used the LEFT
and Right functions but I figure because it ignores zeros after the decimal
place the calculations are not right.
Jorge
My Mistake I meant to type 7.35. In answer to yur question, no I won't be
using any converted figure in any calculations. I've tried to create an alert
to signal when a set number of hours has been exceeded. I've used the LEFT
and Right functions but I figure because it ignores zeros after the decimal
place the calculations are not right.
Jorge
B
Biff
Try this:
The result is numeric and can be used in other calcs: (but then you'd have
to figure out how to deal with values like these when added together: 3.50 +
3.10)
=INT(A1)+TRUNC(ROUND(MOD(A1,1),2)*0.6,2)
7.35 = 7.21
Biff
The result is numeric and can be used in other calcs: (but then you'd have
to figure out how to deal with values like these when added together: 3.50 +
3.10)
=INT(A1)+TRUNC(ROUND(MOD(A1,1),2)*0.6,2)
7.35 = 7.21
Biff
B
bpeltzer
It seems as though you're entering hours in decimal form (that is, you're
entering numbers), and want to get back a number where the digits after the
decimal indicate the number of minutes. If that's the case, I believe the
formula =TRUNC(A5)+MOD(A5,1)*3/5 should do it (I've used A5 as the input
cell; you'll obviously adjust). You probably want to format the result as a
number with two decimal places.
--Bruce
entering numbers), and want to get back a number where the digits after the
decimal indicate the number of minutes. If that's the case, I believe the
formula =TRUNC(A5)+MOD(A5,1)*3/5 should do it (I've used A5 as the input
cell; you'll obviously adjust). You probably want to format the result as a
number with two decimal places.
--Bruce
B
Biff
format the result as a number with two decimal places.
You have to round in the formula otherwise you could get incorrect results.
Biff
You have to round in the formula otherwise you could get incorrect results.
Biff
J
Jorge
Thanks,
What you've given me produces similar results to Biff's, however when
dealing with negative values e.g. 7.10 hours less (-7.10) than the permitted
balance I get a result of -6.46 using the formula =TRUNC(A5)+MOD(A5,1)*3/5;
and
-7.46 using the formula =INT(B43)+TRUNC(ROUND(MOD(B43,1),2)*0.6,2)
We're almost there
Jorge
What you've given me produces similar results to Biff's, however when
dealing with negative values e.g. 7.10 hours less (-7.10) than the permitted
balance I get a result of -6.46 using the formula =TRUNC(A5)+MOD(A5,1)*3/5;
and
-7.46 using the formula =INT(B43)+TRUNC(ROUND(MOD(B43,1),2)*0.6,2)
We're almost there
Jorge
J
Jorge
Thanks,
What you've given me produces similar results to bpeltzer's, however when
dealing with negative values e.g. 7.10 hours less (-7.10) than the permitted
balance I get a result of -6.46 using the formula =TRUNC(A5)+MOD(A5,1)*3/5;
and
-7.46 using the formula =INT(B43)+TRUNC(ROUND(MOD(B43,1),2)*0.6,2)
We're almost there.
Jorge
What you've given me produces similar results to bpeltzer's, however when
dealing with negative values e.g. 7.10 hours less (-7.10) than the permitted
balance I get a result of -6.46 using the formula =TRUNC(A5)+MOD(A5,1)*3/5;
and
-7.46 using the formula =INT(B43)+TRUNC(ROUND(MOD(B43,1),2)*0.6,2)
We're almost there.
Jorge
B
Biff
Try this:
=(INT(ABS(A1))+TRUNC(ROUND(MOD(ABS(A1),1),2)*0.6,2))*IF(A1<0,-1,1)
Biff
=(INT(ABS(A1))+TRUNC(ROUND(MOD(ABS(A1),1),2)*0.6,2))*IF(A1<0,-1,1)
Biff
J
Jorge
Hi Bruce,
I've found that you're formula does'nt work with single digit decimal
negative figures - eg a mimum permitted balance of -8.16 equates to -7.50
instead of -8.09.
I hope someone is able to figure out this bit dealing with minus/negative
balances.
Jorge
I've found that you're formula does'nt work with single digit decimal
negative figures - eg a mimum permitted balance of -8.16 equates to -7.50
instead of -8.09.
I hope someone is able to figure out this bit dealing with minus/negative
balances.
Jorge
J
Jorge
Thanks Biff,
Your formula is spot on now, working with all figures negative and positive.
It's also given me some insight into dealing with some new scenarios.
Very much appreciated.
Jorge
Your formula is spot on now, working with all figures negative and positive.
It's also given me some insight into dealing with some new scenarios.
Very much appreciated.
Jorge
B
Biff
You're welcome. Thanks for the feedback!
Biff
Biff
Jorge said:Thanks Biff,
Your formula is spot on now, working with all figures negative and
positive.
It's also given me some insight into dealing with some new scenarios.
Very much appreciated.
Jorge