Mathematical Time Conversion Formula
 

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.

Mathematical Time Conversion Formula
 

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.

Mathematical Time Conversion Formula
 

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

Mathematical Time Conversion Formula
 

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

Mathematical Time Conversion Formula
 

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 :confused:


--
Chuck45
------------------------------------------------------------------------
Chuck45's Profile: http://www.excelforum.com/member.php...o&userid=28065
View this thread: http://www.excelforum.com/showthread...hreadid=476391

Mathematical Time Conversion Formula
 

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" wrote in message
...

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 :confused:


--
Chuck45
------------------------------------------------------------------------
Chuck45's Profile: http://www.excelforum.com/member.php...o&userid=28065
View this thread: http://www.excelforum.com/showthread...hreadid=476391

Mathematical Time Conversion Formula
 

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" wrote in
message ...

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 :confused:


--
Chuck45
------------------------------------------------------------------------
Chuck45's Profile:
http://www.excelforum.com/member.php...o&userid=28065
View this thread: http://www.excelforum.com/showthread...hreadid=476391