Time conversion

%3CLINGO-SUB%20id%3D%22lingo-sub-2128453%22%20slang%3D%22en-US%22%3ETime%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2128453%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3EI%20have%20a%20list%20of%20time%20in%20the%20format%2000%3A15%2C%20meaning%200%20minuts%20and%2015%20seconds%20followed%20by%20a%20value%20in%20the%20second%20cell.%20I%20have%20to%20construct%20a%20graph%20with%20time%20as%20X-axes%20and%20value%20in%20Y-axes.%3C%2FP%3E%3CP%3EWhen%20the%20time%20reach%2023%3A45%20and%20the%20following%20is%2024%20minuts%200%20sec%2C%20excel%20convert%20it%20in%2000%3A00%2C%20meaning%20that%20is%20a%20new%20days.%3C%2FP%3E%3CP%3EI%20tried%20many%20formulas%2C%20but%20always%20incorrect.%3C%2FP%3E%3CP%3EWhat%20format%20I%20have%20to%20assig%20to%20the%20time%20cell%3F%20How%20can%20I%20use%20the%20time%20date%20for%20the%20X-axes%20in%20the%20graph%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2128453%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2128632%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2128632%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F964896%22%20target%3D%22_blank%22%3E%40francesco1620%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20default%2C%2000%3A15%20is%200%20hours%20and%2015%20minutes.%20You%20have%20to%20enter%200%20minutes%20and%2015%20seconds%20as%2000%3A00%3A15%2C%20even%20if%20you%20only%20display%20the%20minutes%20and%20seconds.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20really%20meant%200%20minutes%20and%2015%20seconds%2C%20apply%20the%20custom%20number%20format%20%5Bmm%5D%3Ass%3C%2FP%3E%0A%3CP%3EThis%20allows%20the%20minutes%20to%20be%20above%2059.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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 .