SOLVED

Time calculation

New Contributor

Hi,

I tried to calculate time but problem is: shell calculate only 15 min steps times.

 

ex. 

=ARVO(KOKONAISLUKU(SUMMA('C26:W26))&":"&JAKOJ(SUMMA(C26:W26);1)*60)

 

Shell type is own: [h]:mm

 

0,20:12
1,251:15
8,58:30
23,523:30
49,549:30
123,5123:30
49,60:49
155,92:35
7 Replies

@Hannu_Siirala 

When I try to translate the formula, most functions are recognized but JAKOJ is unknown. Do you know what it is in English?

@Hannu_Siirala 

Thank you. I see now that there is a misplaced ' before C26, this threw off the translation.

Can you explain the problem more clearly?

Wronk calculation, only 0, 0.25, 0.5, 0.75 are correct.

=ARVO(INT(A8)&":"&MOD(A8;1)*60)
Shell type is Custom: [h]:mm
A B
0,20 0:12 correct
1,25 1:15 correct
123,80 2:03
197,00 197:00 Correct
200,10 3:20
271,20 4:31
271,25 271:15 Correct
345,30 5:45

@Hannu_Siirala 

You could use =A8/24 and format the cell with the formula as [h]:mm

best response confirmed by Hannu_Siirala (New Contributor)
Solution

@Hannu_Siirala 

If you want to correct your formula:

=ARVO(KOKONAISLUKU(A8)&":"&PYÖRISTÄ(JAKOJ(A8;1)*60;0)&":00")

THANK

now it works