Home

Input style in custom number formatted cell seconds.milliseconds possible?

%3CLINGO-SUB%20id%3D%22lingo-sub-255387%22%20slang%3D%22en-US%22%3EInput%20style%20in%20custom%20number%20formatted%20cell%20seconds.milliseconds%20possible%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-255387%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20custom%20my%20number%20format%20as%3C%2FP%3E%3CP%3E%3CEM%3Ess.00%3B%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20when%20I%20input%20my%20time%20in%20my%20cell%26nbsp%3Bas%26nbsp%3Bseconds.hundreths%2C%20it%20returns%20nothing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20way%20it%20worked%20was%20for%20me%20to%20input%20my%20time%20as%20minutes(0)%3Aseconds.hundreths.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20not%20a%20way%20for%20me%20to%20only%20enter%20the%20desired%20data%2C%20as%20seconds.hundreths%3F%20Bypassing%20the%200minutes%3A...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20I've%20seen%20in%20some%20custom%20fields%20the%20symbol%20%40%20at%20the%20end%20of%26nbsp%3Bit%20%3A%26nbsp%3Bwhat%20does%20that%26nbsp%3Bstands%26nbsp%3Bfor%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-255387%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormative%20Assessments%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormatting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Einput%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-256887%22%20slang%3D%22en-US%22%3ERe%3A%20Input%20style%20in%20custom%20number%20formatted%20cell%20seconds.milliseconds%20possible%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-256887%22%20slang%3D%22en-US%22%3EExcel%20isn't%20great%20with%20entering%20times%20less%20than%20a%20second.%20I%20would%20enter%20the%20seconds%20as%20ordinary%20numbers%20and%20then%20when%20I%20need%20to%20do%20math%20with%20them%2C%20divide%20by%20(24*60*60)%20to%20convert%20them%20to%20%22Excel%20time%22.%20Excel%20holds%20time%20as%20a%20fraction%20of%20an%20entire%20day%3A%20the%20number%201%20equals%2024%20hours%20equals%2024*60%20minutes%20equals%2024*60*60%20seconds.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766056%22%20slang%3D%22en-US%22%3ERe%3A%20Input%20style%20in%20custom%20number%20formatted%20cell%20seconds.milliseconds%20possible%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766056%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F110995%22%20target%3D%22_blank%22%3E%40Thomas%20Littzen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EInterestingly%2C%20the%20%3CSTRONG%3Ess.00%3C%2FSTRONG%3Enotation%20works%20in%20the%20latest%20version%20of%20Office%20365%20(screenshot%20attached)%20-%20what%20version%20of%20Excel%20are%20you%20in%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%26nbsp%3B%3CSTRONG%3E%40%3C%2FSTRONG%3Esymbol%20is%20simply%20a%20text%20placeholder.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECheck%20out%20more%20at%20%3CA%20href%3D%22http%3A%2F%2Fwww.sumproduct.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.sumproduct.com%3C%2FA%3Eand%20%3CA%20href%3D%22http%3A%2F%2Fwww.sumproduct.com%2Fthought%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.sumproduct.com%2Fthought%3C%2FA%3Efor%20free%20file%20download%20examples.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Thomas Littzen
New Contributor

Hello,

 

I want to custom my number format as

ss.00;

 

But when I input my time in my cell as seconds.hundreths, it returns nothing.

 

One way it worked was for me to input my time as minutes(0):seconds.hundreths.

 

Is there not a way for me to only enter the desired data, as seconds.hundreths? Bypassing the 0minutes:...

 

Also, I've seen in some custom fields the symbol @ at the end of it : what does that stands for?

 

Thank you.

2 Replies
Excel isn't great with entering times less than a second. I would enter the seconds as ordinary numbers and then when I need to do math with them, divide by (24*60*60) to convert them to "Excel time". Excel holds time as a fraction of an entire day: the number 1 equals 24 hours equals 24*60 minutes equals 24*60*60 seconds.

@Thomas Littzen 

 

Interestingly, the ss.00 notation works in the latest version of Office 365 (screenshot attached) - what version of Excel are you in?

 

The @ symbol is simply a text placeholder.

 

Check out more at www.sumproduct.com and www.sumproduct.com/thought for free file download examples.