hh:mm to hours.00|25|50|75

[edpatterson]

Excell 2000

I need to convert cells from a time sheet that are formatted as hh:mm to a number which would be hours.decimal rounded to nearest 1/4 hour.

Minutes
53 -> 60 = .00
00 -> 07 = .00
08 -> 22 = .25
23 -> 37 = .50
38 -> 52 = .75

so, 8:43 in one cell would display as 8.75 in another.

Ideas?

Ed

 

[philthepowerhouse]

Hi there,

I'm no expert but..

Is there nothing in the Format-cells-number/time or custom options?

Powerhouse

 

[xlbo]

You will need the Analysis Toolpak addin to be installed but

=MROUND(A1*24,0.25)

appears to work nicely

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

Hi Ed,

MROUND (in the Analysis ToolPak) is the function you want ..
[blue][tt] =MROUND(A2,TIMEVALUE("00:15"))[/tt][/blue]

This will give you a result in DAYS - to get hours, multiply by 24 ..
[blue][tt] =MROUND(A2,TIMEVALUE("00:15"))*24[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[shetlandbob]

More proof that you learn something new everyday, as I was thinking about writing lots of IF statements etc......

Robert Cumming

http://www.consultantengineer.co.uk

 

[edpatterson]

Once again the fine members of Tek-Tips comes through.

I will see if there is a list of functions provided by the analysis tool pack.

I was trying various themes on right() left() find() yada, yada, yada

Thanks,
Ed

 

[xlbo]

MROUND() & NETWORKDAYS() are imho, the most useful...

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question