3 Thousandths 😱😱😱
Looking at results of speed skating in the Olympic winter games of 2014, the gap between the first and second of the 1500m race was only 3 thousandths.
Separate athletes with thousandth is extremely unusual and this give me the idea to calculate what represent 0.003 after 1500m race.
With Excel, it's easy to calculate the difference between two times. You just have to use the proper format for the time.
We know the distance (1500m) and the time for the athletes. Let's create the following table with the time of the athletes
We see immediately the first problem: we have lost the thousandths 😱😭😡
Format of the tenths, hundredths and thousandths
In Excel, if you want to visualize the tenths, hundredths or thousandths to your Time, you need to add the value 0. Have a look at this article to see how to customize your time format.
- Selecting the two cells that contain the time
- Click on the little arrow to the right of the word number in the Ribbon to display the dialog box cell format. Or open the format dialog box with the shortcut Ctrl + 1
In the dialog Cell format, you select in the left menu, the Custom option in the text box, you write your own number format, as in this case
And instantly you see the thousandths in your spreadsheet 😍😍👍
Convert in seconds
In Excel, 1 is always 1 day and never 1 hour or 1 minute.
To solve our problem, we must convert the time in B4 by the number of seconds.
The only way to keep the thousandths is to use this formula.
- 24 is the number of hours in a day
- 60 is the number of minutes in an hour
- 60 is the number of seconds in a minute
And we obtain C4 cell the time in seconds, with thousandths
Distance in 1 second
We have nearly finished 😃😉
Now, we have to calculate the distance covered by each athlete in 1 second. The formula is simple: a division between the distance and the time
Wow! An average of 14.28 meter per second , that's huuuuuuuge 😲😲😲
Difference between the two athletes
We just have to calculate the gap between each competitor
Gap for the race
And finally multiply this result by the running time
And we get a difference of 0.0429 m or 4.29 cm
27/03/2019 @ 02:48
how do I convert this text string "2019-03-24 15:00:28 759" into a date/time fromat in excel???