In A1 cell I have time as 00:44:12 [hh:mm:ss in format cells]. I have distance in cell A2 as 12,1. When I put equation =A2/A1*20 it gives me wrong answer. From my understanding it treats the time as mm,ss which is 44,12 minutes instead 44 minutes and 12 seconds.
In one example the right answer is 5,475 but the equation gives 5,485.
If the time represents a duration rather than a clock time, you should prefer number format [HH]:MM:SS over HH:MM:SS.
When the duration is beyond 24 hours, the former shows 30:00:00 wheras the latter shows 06:00:00 (the clock time after 24 hours). It makes no difference with values smaller than 24 hours.
A spreadsheet has no date values nor times. There are only day numbers and fractions of days.
0 → 1899-12-30 00:00
1 → 1899-12-31 00:00
2 → 1900-01-01 00:00
2.5 → 1900-01-01 12:00
…
44976.75 → 2023-02-19 18:00
Therefore you always calculate with numbers. The difference between 2 dates or times is returned as the difference in unit “days” and =WEEKDAY(44976) returns 1 (Sunday) because day number 44976 is a Sunday.
Thanks for the answer but how do I fix it? Changing from hh:mm:ss to [hh]:mm:ss doesn’yt change anything
@karolus provided the formula calculating distance per hour. A2/A1 is divided by 24 because we do calculate with days which need to be converted into hours.
so now I have =A2/A1/24/60*20 which for some reason is still slightly wrong. On one example I should get 1948 but I get 1952
it is close enough so I’ll accept this. Thanks once again!
Hallo
If you want the distance per hour ? do:
=A2/A1/24
I have time and distance. I want an average distance over y time. Basically distance/time*20 where 20 is 20 minutes