coverting h:mm to decimal
 

I have a spreadsheet with several "time" columns. I then bring this data to
a pivot table. Before doing that, I have another column set up that converts
h:mm to a decimal format (i.e: 3:30 convert to 3.5).

I do this conversion with the following formula:
=(P5875-INT(P5875))*24

This works for most times, except for anything that is 24:00 or greater. In
this case, it subtracts 24 from the total (i.e., 24:00 converts to 0.00,
26:25 converts to 2.25).

Is there another formula I can use that will correctly convert ALL times?

Thank you

coverting h:mm to decimal
 

=(P5875-INT(P5875))*24

It depends on what's in P5875 and why you're subtracting the INT. Is P5875 a
date + time? If so, when you subtract the INT the time will never be more
than 24 hrs.

Give us an example of an entry that should be 24 hrs.


--
Biff
Microsoft Excel MVP


"richzip" wrote in message
...
I have a spreadsheet with several "time" columns. I then bring this data
to
a pivot table. Before doing that, I have another column set up that
converts
h:mm to a decimal format (i.e: 3:30 convert to 3.5).

I do this conversion with the following formula:
=(P5875-INT(P5875))*24

This works for most times, except for anything that is 24:00 or greater.
In
this case, it subtracts 24 from the total (i.e., 24:00 converts to 0.00,
26:25 converts to 2.25).

Is there another formula I can use that will correctly convert ALL times?

Thank you

coverting h:mm to decimal
 

Change your formula to:

=P5875*24

That will pick up all hours in the cell. (By the way, the reason your
original formula subtracted 24 from the total is because you told it to --
that's what the INT portion does.)

Regards,
Fred.

"richzip" wrote in message
...
I have a spreadsheet with several "time" columns. I then bring this data
to
a pivot table. Before doing that, I have another column set up that
converts
h:mm to a decimal format (i.e: 3:30 convert to 3.5).

I do this conversion with the following formula:
=(P5875-INT(P5875))*24

This works for most times, except for anything that is 24:00 or greater.
In
this case, it subtracts 24 from the total (i.e., 24:00 converts to 0.00,
26:25 converts to 2.25).

Is there another formula I can use that will correctly convert ALL times?

Thank you