Time conversion

Occasional Visitor

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 

 

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.

@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 .