formatting cells

[excelguy]

In cell C2 I wrote the formula ((C1-365)*24) where C1 is (365.25) thus the
value for cell C2 is 6. what I want to do is format cell C2 so that it will
be shown as 6 with a PM next to it. I want this formula to work for all
values that I put for cell C1. For example, if cell C1 equals 365.3468, the
formula in cell C2 would yeild a value 8.3232 but excel would format this
value for me into a time - 8:19:23.52 PM

 

[joel]

First don't multiply by 24. I assume 24 is hours per day.

In Excel a Day = 1, and an hour = 1/24.

So 365.25 = 365 = .25 which is a quarter of a day or 6:00 AM.

Now if you have a date/Time combined and want to get hours you need only the
fractional part of the Date/Time

3995.34 (3995 is the date and .34 is the hours)

Use the MOD() function

Mod(3995.34,1) = .34

If you format the .34 as time it will display as hours minutes seconds.