Forum Discussion
Time conversion
Hello
I have a list of time in the format 00:15, meaning 0 minuts and 15 seconds followed by a value in the second cell. I have to construct a graph with time as X-axes and value in Y-axes.
When the time reach 23:45 and the following is 24 minuts 0 sec, excel convert it in 00:00, meaning that is a new days.
I tried many formulas, but always incorrect.
What format I have to assig to the time cell? How can I use the time date for the X-axes in the graph
2 Replies
francesco1620 You can enter 0m 15s in the form 0:15.0 . The presence of a decimal fraction allows Excel to interpret that as 0m 15.0s.
(FYI, in this form, Excel permits seconds to have a precision of up to only 3 decimal places -- milliseconds. If we enter more precision, Excel rounds to 3 decimal places. For example, 12:34.5678 is interpreted exactly the same as 12:34.568.)
You might want to change the default format from mm:ss.0 to [m]:ss .
It is prudent to always format the first time specifier (h, m or s) in square-brackets, just in case the calculated or entered exceeds the limit of the specifier (23 for h; 59 for m and s), by accident or on purpose.
For example, if we enter 765:34.0 (765m 34s), Excel displays 45:34.0 by default because 765m 34s is indeed 12h 45m 34s.
If we change the format to [m]:ss , we see the time as we entered it, namely 765:34 .
By default, 00:15 is 0 hours and 15 minutes. You have to enter 0 minutes and 15 seconds as 00:00:15, even if you only display the minutes and seconds.
If you really meant 0 minutes and 15 seconds, apply the custom number format [mm]:ss
This allows the minutes to be above 59.
