Change base 6 to base 10?

[Brian Clarke]

A report I get regularly lists amounts of time in hours and parts of an
hour. However, the "parts of an hour" are units of 6 minutes, while the time
is listed in "pseudo-decimal" format, like this:

90 minutes would be 1.3 hours
12 minutes would be 0.12 hours

and so on.

I am trying to get the right formula to translate these into decimal hours,
so that

90 minutes would be 1.5 hours
12 minutes would be 0.2 hours

and so on.

The following works for positive numbers, but not negative numbers.

=INT(H2)+((H2-INT(H2))*10/6)

I have tried adapting this formula, but I am getting nowhere. Any
suggestions would be appreciated.

[Stefi]

Do you mean this?

=(INT(ABS(H2))+((ABS(H2)-INT(ABS(H2)))*10/6))*SIGN(H2)

Regards,
Stefi

„Brian Clarke” ezt �*rta:

> A report I get regularly lists amounts of time in hours and parts of an
> hour. However, the "parts of an hour" are units of 6 minutes, while the time
> is listed in "pseudo-decimal" format, like this:
>
> 90 minutes would be 1.3 hours
> 12 minutes would be 0.12 hours
>
> and so on.
>
> I am trying to get the right formula to translate these into decimal hours,
> so that
>
> 90 minutes would be 1.5 hours
> 12 minutes would be 0.2 hours
>
> and so on.
>
> The following works for positive numbers, but not negative numbers.
>
> =INT(H2)+((H2-INT(H2))*10/6)
>
> I have tried adapting this formula, but I am getting nowhere. Any
> suggestions would be appreciated.
>
>
>
>

[Brian Clarke]

Yes, that's it! Many thanks for your help. Even after 10+ years of Excel, I
am still learning. I wasn't aware of the SIGN function.


"Stefi" <[email protected]> wrote in message
news:[email protected]...
> Do you mean this?
>
> =(INT(ABS(H2))+((ABS(H2)-INT(ABS(H2)))*10/6))*SIGN(H2)
>
> Regards,
> Stefi
>
> "Brian Clarke" ezt �rta:
>
> > A report I get regularly lists amounts of time in hours and parts of an
> > hour. However, the "parts of an hour" are units of 6 minutes, while the
time
> > is listed in "pseudo-decimal" format, like this:
> >
> > 90 minutes would be 1.3 hours
> > 12 minutes would be 0.12 hours
> >
> > and so on.
> >
> > I am trying to get the right formula to translate these into decimal
hours,
> > so that
> >
> > 90 minutes would be 1.5 hours
> > 12 minutes would be 0.2 hours
> >
> > and so on.
> >
> > The following works for positive numbers, but not negative numbers.
> >
> > =INT(H2)+((H2-INT(H2))*10/6)
> >
> > I have tried adapting this formula, but I am getting nowhere. Any
> > suggestions would be appreciated.
> >
> >
> >
> >

[Andre Croteau]

Hi Brian,

try this:

=(INT(ABS(H2))+((ABS(H2)-INT(ABS(H2)))*10/6))*(H2/ABS(H2))

This will work for both negative and positive.

rgds

Andr�

"Brian Clarke" <[email protected]> wrote in message
news:[email protected]...
>A report I get regularly lists amounts of time in hours and parts of an
> hour. However, the "parts of an hour" are units of 6 minutes, while the
> time
> is listed in "pseudo-decimal" format, like this:
>
> 90 minutes would be 1.3 hours
> 12 minutes would be 0.12 hours
>
> and so on.
>
> I am trying to get the right formula to translate these into decimal
> hours,
> so that
>
> 90 minutes would be 1.5 hours
> 12 minutes would be 0.2 hours
>
> and so on.
>
> The following works for positive numbers, but not negative numbers.
>
> =INT(H2)+((H2-INT(H2))*10/6)
>
> I have tried adapting this formula, but I am getting nowhere. Any
> suggestions would be appreciated.
>
>
>

[Stefi]

You are welcome! Thanks for the feedback!
Stefi


„Brian Clarke” ezt �*rta:

> Yes, that's it! Many thanks for your help. Even after 10+ years of Excel, I
> am still learning. I wasn't aware of the SIGN function.
>
>
> "Stefi" <[email protected]> wrote in message
> news:[email protected]...
> > Do you mean this?
> >
> > =(INT(ABS(H2))+((ABS(H2)-INT(ABS(H2)))*10/6))*SIGN(H2)
> >
> > Regards,
> > Stefi
> >
> > "Brian Clarke" ezt �*rta:
> >
> > > A report I get regularly lists amounts of time in hours and parts of an
> > > hour. However, the "parts of an hour" are units of 6 minutes, while the
> time
> > > is listed in "pseudo-decimal" format, like this:
> > >
> > > 90 minutes would be 1.3 hours
> > > 12 minutes would be 0.12 hours
> > >
> > > and so on.
> > >
> > > I am trying to get the right formula to translate these into decimal
> hours,
> > > so that
> > >
> > > 90 minutes would be 1.5 hours
> > > 12 minutes would be 0.2 hours
> > >
> > > and so on.
> > >
> > > The following works for positive numbers, but not negative numbers.
> > >
> > > =INT(H2)+((H2-INT(H2))*10/6)
> > >
> > > I have tried adapting this formula, but I am getting nowhere. Any
> > > suggestions would be appreciated.
> > >
> > >
> > >
> > >
>
>
>