Mathematical Time Conversion Formula

[stacy]

Hello Everyone...

I have a question about converting a segment of time between minutes,
and 100th's. Let me try to explain more clearly.

We have two places that keeps track of an employee's earned time off
(ETO). On their pay stub, the amount is tracked in 100th's of an hour
(may not be saying that correctly). So, an employee can have 21.84
hours of ETO, but this is actually 21 hours and 50 minutes (.84*.6).
So, in one location, the employee can see 21.84 hours, and in another
they see 21.50 hours... Here in lies the issue... I can easily take the
..xx numbers and multiply by .6 to convert to actual minutes. However,
how can I write a formaula that looks at the whole number (21.84), and
converts it in another cell to read 21.50? Is there an easy way?

Cell A1 Cell A2
21.84 (formula that shows 21.50)

As always, thanks ahead of time for any assistance.

[Gary''s Student]

If 21.84 is in A1, then

=60*(A1-INT(A1))/100+INT(A1)

will get the desired value
--
Gary's Student


"stacy" wrote:

> Hello Everyone...
>
> I have a question about converting a segment of time between minutes,
> and 100th's. Let me try to explain more clearly.
>
> We have two places that keeps track of an employee's earned time off
> (ETO). On their pay stub, the amount is tracked in 100th's of an hour
> (may not be saying that correctly). So, an employee can have 21.84
> hours of ETO, but this is actually 21 hours and 50 minutes (.84*.6).
> So, in one location, the employee can see 21.84 hours, and in another
> they see 21.50 hours... Here in lies the issue... I can easily take the
> ..xx numbers and multiply by .6 to convert to actual minutes. However,
> how can I write a formaula that looks at the whole number (21.84), and
> converts it in another cell to read 21.50? Is there an easy way?
>
> Cell A1 Cell A2
> 21.84 (formula that shows 21.50)
>
> As always, thanks ahead of time for any assistance.
>
>

[stacy]

Awesome!! I know this was probably trivial, but it is exactly what I
needed!!! Thanks again for sharing this!!!

[[email protected]]

Stacy:

This was a bit tougher than I thought but this formula does the
trick....

=SUM(ROUNDDOWN(_100ths,0))+ROUND(SUM(_100ths,-SUM(ROUNDDOWN(_100ths,0)))*0.6,2)

I like to use Named Ranges in Big Formulas, they make more sense.
If you simply enter "A1" wherever you see "_100ths" then this will
solve your quandry...Mark

[Chuck45]

I had a similar problem as Stacey. I enter data as 60ths of an hour, ex. 5.30 and 2.30, but the total comes out as 7.6 and I need it to read 8.0 as eight hours worked. Is the format wrong or is there simply a calculation I can enter to convert the time?

Thanks in advance.
chuck

[David McRitchie]

entering 2.30 instead of 2:30 certainly is wrong.

Time is measured as a fraction of a day. if you
have a time of 8:00 and you want to convert to a decimal
number you would multiply by 24.

More information on Date and Time
http://www.mvps.org/dmcritchie/excel/datetime.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Chuck45" <[email protected]> wrote in message
news:[email protected]...
>
> I had a similar problem as Stacey. I enter data as 60ths of an hour,
> ex. 5.30 and 2.30, but the total comes out as 7.6 and I need it to
> read 8.0 as eight hours worked. Is the format wrong or is there simply
> a calculation I can enter to convert the time?
>
> Thanks in advance.
> chuck
>
>
> --
> Chuck45
> ------------------------------------------------------------------------
> Chuck45's Profile: http://www.excelforum.com/member.php...o&userid=28065
> View this thread: http://www.excelforum.com/showthread...hreadid=476391
>

[Tom Ogilvy]

If you have the analysis toolpak installed, you can use

A1: 5.30
B1: 2.30

=DOLLARFR(DOLLARDE(A1,60)+DOLLARDE(B1,60),60)

--
Regards,
Tom Ogilvy


"Chuck45" <[email protected]> wrote in
message news:[email protected]...
>
> I had a similar problem as Stacey. I enter data as 60ths of an hour,
> ex. 5.30 and 2.30, but the total comes out as 7.6 and I need it to
> read 8.0 as eight hours worked. Is the format wrong or is there simply
> a calculation I can enter to convert the time?
>
> Thanks in advance.
> chuck
>
>
> --
> Chuck45
> ------------------------------------------------------------------------
> Chuck45's Profile:
http://www.excelforum.com/member.php...o&userid=28065
> View this thread: http://www.excelforum.com/showthread...hreadid=476391
>