Time convertion function question

[Sergey]

Hi people,

does anyone know how to convert, say 3:15pm to 3.25, or
5:45am to 5.75 in Excel. General question is How to
represent quarter of hour as 0.25.

Thanks much,
Sergey

 

[Peo Sjoblom]

If your values are numeric time values multiply with 24 and format as
general
That of course means that 3:15 PM equals 15.25 If you want a value that is
not
the true values use

=IF(F1*24>12,F1*24-12,F1*24)

or less long

=MOD(F1*24,12)

 

[Lorrie]

Peo -

You helped me with this question a couple of days ago and
I still can't get it to work correctly. I've tried
everything that you have suggested.

I have six cells the have time entered in them.
MORNING AFTERNOON EVENING Total hours
In Out IN Out In Out
8:45AM 10:00AM|11:00AM 5:15PM |6:00PM 6:15PM 7:45

The formula to add up the time looks like this:
=SUM(D10-C10)+(F10-E10)+(H10-G10)
The result is 7:45.

In order to continue I need the result to be 7.75. Can
that be done?
None of the suggestions have worked for my situation.

The =MOD(F1*24,12) does help, even as a nested formula
The longer formula that you suggested for this person
produced a result of 0:00.

I'd really be in your debt if you could help me ge this
figured out.

 

[JohnI in Brisbane]

Lorrie,

Try either of the following formulas_

=((D10-C10)+(F10-E10)+(H10-G10))*24

=SUM((D10-C10),(F10-E10),(H10-G10))*24

Time is stored as a fraction of a day, so multiplying by 24 will return the
result you are after. i.e. 7.75

regards,

JohnI