Converting Decimal to Time

I have the decimal number 12.73 and I want it to show as time, which should
be 13.13 ( 13 Hours and 13 Minutes). How do I do this?

I think you want to divide it by 24 but I cannot see where 13:13 comes from,
I get 12:43.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Charlene" <Charlene@discussions.microsoft.com> wrote in message
news:60DBFA67-47B2-4907-BE89-D8D2266A346C@microsoft.com...
> I have the decimal number 12.73 and I want it to show as time, which
should
> be 13.13 ( 13 Hours and 13 Minutes). How do I do this?

It seems like your example is not a typical conversiion where 12.73 would be
12.73 hours.

If 12.73 means 12 hours and 73 minutes, they try this:

For a value in A1

B1: =(INT(A1)*60+(MOD(A1,1)*100))/(24*60)
or
B1: =(INT(A1)+(MOD(A1,1)*100/60))/(24)
Format the results as time

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Charlene" wrote:

> I have the decimal number 12.73 and I want it to show as time, which should
> be 13.13 ( 13 Hours and 13 Minutes). How do I do this?

No, the result was 12:00:00 AM. Thank you for trying


"Ron Coderre" wrote:

> It seems like your example is not a typical conversiion where 12.73 would be
> 12.73 hours.
>
> If 12.73 means 12 hours and 73 minutes, they try this:
>
> For a value in A1
>
> B1: =(INT(A1)*60+(MOD(A1,1)*100))/(24*60)
> or
> B1: =(INT(A1)+(MOD(A1,1)*100/60))/(24)
> Format the results as time
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Charlene" wrote:
>
> > I have the decimal number 12.73 and I want it to show as time, which should
> > be 13.13 ( 13 Hours and 13 Minutes). How do I do this?

> No, the result was 12:00:00 AM. Thank you for trying

I don't know how that could happen....Check the formula.

For A1: 12.73

B1: =(INT(A1)+(MOD(A1,1)*100/60))/(24)
B1: =(12 + 0.73*100/60)/24
B1: =(12 + 1.216666...)/24
B1: =(13.216666...)/24
B1: =0.550694444444444
(slightly more than half a day)
Formatted as time, that is 13:13:00

***********
Regards,
Ron

XL2002, WinXP-Pro


"Charlene" wrote:

> No, the result was 12:00:00 AM. Thank you for trying
>
>
> "Ron Coderre" wrote:
>
> > It seems like your example is not a typical conversiion where 12.73 would be
> > 12.73 hours.
> >
> > If 12.73 means 12 hours and 73 minutes, they try this:
> >
> > For a value in A1
> >
> > B1: =(INT(A1)*60+(MOD(A1,1)*100))/(24*60)
> > or
> > B1: =(INT(A1)+(MOD(A1,1)*100/60))/(24)
> > Format the results as time
> >
> > Does that help?
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP-Pro
> >
> >
> > "Charlene" wrote:
> >
> > > I have the decimal number 12.73 and I want it to show as time, which should
> > > be 13.13 ( 13 Hours and 13 Minutes). How do I do this?

=--SUBSTITUTE(A1,".",":")

format the cell as time or you'll get some decimals, I must say the logic in
displaying 13:13 as 12.73 escapes me?

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com




"Charlene" <Charlene@discussions.microsoft.com> wrote in message
news:60DBFA67-47B2-4907-BE89-D8D2266A346C@microsoft.com...
>I have the decimal number 12.73 and I want it to show as time, which should
> be 13.13 ( 13 Hours and 13 Minutes). How do I do this?

Maybe I'm doing this wrong. Column A reads like this

Week 1 39.00 hours
2 39.00 hours
3 39.00 hours
4 39.00 hours
5 39.00 hours
6 39.00 hours
Total 280.14 hours
Divided by 22.00 per hour
Total 12.73 hours



"Peo Sjoblom" wrote:

> =--SUBSTITUTE(A1,".",":")
>
> format the cell as time or you'll get some decimals, I must say the logic in
> displaying 13:13 as 12.73 escapes me?
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
>
>
> "Charlene" <Charlene@discussions.microsoft.com> wrote in message
> news:60DBFA67-47B2-4907-BE89-D8D2266A346C@microsoft.com...
> >I have the decimal number 12.73 and I want it to show as time, which should
> > be 13.13 ( 13 Hours and 13 Minutes). How do I do this?
>
>
>

"Charlene" <Charlene@discussions.microsoft.com> wrote in message
news:911CB23C-9FCC-4C07-A415-0B857EE91897@microsoft.com...
> "Peo Sjoblom" wrote:
>> "Charlene" <Charlene@discussions.microsoft.com> wrote in message
>> news:60DBFA67-47B2-4907-BE89-D8D2266A346C@microsoft.com...
>> >I have the decimal number 12.73 and I want it to show as time, which
>> >should
>> > be 13.13 ( 13 Hours and 13 Minutes). How do I do this?

>> =--SUBSTITUTE(A1,".",":")
>>
>> format the cell as time or you'll get some decimals, I must say the logic
>> in
>> displaying 13:13 as 12.73 escapes me?

> Maybe I'm doing this wrong. Column A reads like this
>
> Week 1 39.00 hours
> 2 39.00 hours
> 3 39.00 hours
> 4 39.00 hours
> 5 39.00 hours
> 6 39.00 hours
> Total 280.14 hours
> Divided by 22.00 per hour
> Total 12.73 hours

I'm thoroughly confused. If you've got 6 lots of 39 hours, that adds up to
234 hours, not 280.14. What does the formula in that cell say, and what
format is in that cell, and in the cells with 39.00 hours (and what does the
formula bar show where you've got 39.00 in the cell)?

But if you do end up with 280.14 and divide it by 22 to give 12.73, then
that's decimal hours, so would be 12 hours & 44 minutes, not 13 hours 13
minutes.
--
David Biddulph