At 11:38 02/10/2008 +0200, Erling Larsen wrote:
I'm working on a spreadsheet for time-reporting.
The result must be converted to decimal with two digits, and I'm
doing this with:
AVRUNDA(TIMME(H7)+(((100/60)*MINUT(H7))/100);2)
Where avrunda = Round - timme = hour - minut = minute.
This work fine but my problem is, that i need the second decimal to
be either 0 or 5.
Can this be done?
Yes.
First, your expression has a number of unnecessary parentheses; your
multiplication and division by 100 also has no effect. So I can
simplify it (I'll use the English function names) to
=ROUND(HOUR(H7)+MINUTE(H7)/60;2)
And it is probably unnecessary to handle the hours and minutes
separately. Dates and times are stored as numbers of days, so -
unless you actually need to remove any day and second components of
the time explicitly - you can simply multiply it by 24 to convert to hours:
=ROUND(H7*24;2)
Now to your real problem. If you need the second fractional place
always to be 0 or 5, you are effectively rounding to the nearest
twentieth of an hour. An easy way of doing this is to multiply the
value by twenty, round that to the nearest integer, and then divide
by twenty again. This gives:
=ROUND(H7*24*20)/20
or just:
=ROUND(H7*480)/20
(You need to format the cell to display two fractional places if you
are to see the second place when it is a zero, of course - but you knew that.)
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
