Stop Excel interpreting integers in a time-format cell as days?

%3CLINGO-SUB%20id%3D%22lingo-sub-3302341%22%20slang%3D%22en-US%22%3EStop%20Excel%20interpreting%20integers%20in%20a%20time-format%20cell%20as%20days%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302341%22%20slang%3D%22en-US%22%3E%3CP%3EA%20particular%20time%20keeping%20system%20uses%20time%20cells%20(formatted%20as%20%5Bh%5D%3Amm%22%20%22)%20to%20record%20events%20(start%2C%20stop%2C%20etc)%20as%20times%20of%20day.%20With%20a%20colon%20an%20entry%20gets%20interpreted%20as%20HH%3AMM%20even%20in%20the%20absence%20of%20minutes%2C%20e.g.%20%2210%3A%22%20becomes%20%2210%3A00%22%2C%20but%20without%20the%20colon%20it%20is%20treated%20as%20days%2C%20e.g.%20%2210%22%20becomes%20%22240%3A00%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20force%20a%20simple%20integer%20input%20to%20be%20interpreted%20as%20an%20hour%3F%20(preferably%20without%20resorting%20to%20macros%2C%20since%20the%20owner%20of%20the%20XLS%20will%20be%20change-averse)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3302341%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-3302378%22%20slang%3D%22en-US%22%3ERe%3A%20Stop%20Excel%20interpreting%20integers%20in%20a%20time-format%20cell%20as%20days%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302378%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1354737%22%20target%3D%22_blank%22%3E%40TomGoodfellow%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20how%20Excel%20works.%20It%20needs%20a%20%3A%20to%20interpret%20the%20value%20entered%20as%20time.%20If%20you%20enter%20a%20plain%20number%20in%20a%20cell%20formatted%20as%20time%2C%20it%20converts%20that%20number%20to%20days%2C%20since%20time%20is%20stored%20in%20Excel%20as%20a%20fraction%20of%201%20day%20%3D%2024%20hours.%20There%20is%20no%20way%20to%20avoid%20that%20without%20VBA.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3320506%22%20slang%3D%22en-US%22%3ERe%3A%20Stop%20Excel%20interpreting%20integers%20in%20a%20time-format%20cell%20as%20days%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3320506%22%20slang%3D%22en-US%22%3EI%20was%20hoping%20that%20some%20ingenious%20validation%20rule%20could%20fix%20the%20string%20rather%20just%20rejecting%2C%20but%20I%20didn't%20find%20any%20example%20of%20updating%20the%20value%20that%20didn't%20involve%20VBA.%3C%2FLINGO-BODY%3E
New Contributor

A particular time keeping system uses time cells (formatted as [h]:mm" ") to record events (start, stop, etc) as times of day. With a colon an entry gets interpreted as HH:MM even in the absence of minutes, e.g. "10:" becomes "10:00", but without the colon it is treated as days, e.g. "10" becomes "240:00"

 

How can I force a simple integer input to be interpreted as an hour? (preferably without resorting to macros, since the owner of the XLS will be change-averse)

2 Replies

@TomGoodfellow 

This is how Excel works. It needs a : to interpret the value entered as time. If you enter a plain number in a cell formatted as time, it converts that number to days, since time is stored in Excel as a fraction of 1 day = 24 hours. There is no way to avoid that without VBA.

I was hoping that some ingenious validation rule could fix the string rather just rejecting, but I didn't find any example of updating the value that didn't involve VBA.