SOLVED

Inputting formated numbers

%3CLINGO-SUB%20id%3D%22lingo-sub-1547530%22%20slang%3D%22en-US%22%3EInputting%20formated%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1547530%22%20slang%3D%22en-US%22%3E%3CP%3EHelp%20Please-%20I%20have%20a%20lot%20of%20time%20data%20to%20enter%20in%20an%20Excel%20Spreadsheet.%20I%20would%20like%20to%20type%200955%20to%20then%20appear%20as%2009%3A55%20in%20my%20worksheet.%20I%20know%20I%20can%20use%20a%20Custom%20format%20to%20display%20the%20data%20but%20I%20need%20to%20type%20the%20colon%20for%20each%20entry.%20Is%20there%20an%20easy%20way%20to%20avoid%20this%3F%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1547530%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1547535%22%20slang%3D%22en-US%22%3ERe%3A%20Inputting%20formated%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1547535%22%20slang%3D%22en-US%22%3EHello%2C%20using%20Custom%20Formatting%20is%20still%20the%20best%20option%20to%20go%20with.%20Having%20to%20type%20colon%20for%20each%20of%20the%20entry%20is%20time%20consuming.%20Just%20my%20opinion%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1547562%22%20slang%3D%22de-DE%22%3ERe%3A%20Inputting%20formatted%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1547562%22%20slang%3D%22de-DE%22%3Eright%20mousekey%20%3CBR%20%2F%3E%20Format%20cells%20%3CBR%20%2F%3E%20Custom%20%3CBR%20%2F%3E%20Type%3A%20%3CBR%20%2F%3E%2000%20%3A%2000%20%3CBR%20%2F%3E%20OK%20Should%20work%20without%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20problems%2C%20if%20not%20please%20inform%20again.%20%3CBR%20%2F%3E%20If%20it%20works%2C%20please%20mark%20it%20as%20the%20correct%20answer%20so%20that%20other%20who%20interested%20in%20know%20it%20too%20...%20best%20with%20thumbs%20up%20(like)%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20Nikolino%20%3CBR%20%2F%3E%20I%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1554045%22%20slang%3D%22en-US%22%3ERe%3A%20Inputting%20formated%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1554045%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F400880%22%20target%3D%22_blank%22%3E%40Tips1949%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eattached%20Excel%20macroenabled%20workbook.%20Open%20it%20and%20enable%20macros.%3C%2FP%3E%3CP%3EOpen%20that%20worksheet%20where%20you%20are%20going%20to%20input%20timevalues.%3C%2FP%3E%3CP%3EMacro%20starts%20to%20fill%20values%20from%20activecell%20and%20below%20it%20when%20you%20type%20digits%20to%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehit%20ALT%20%2B%20F8%20and%20select%20TimeMachine%20-%20macro%20to%20run.%3C%2FP%3E%3CP%3EMacro%20asks%20you%20how%20many%20timevalues%20you%20want%20to%20type%2C%20answer%20some%20amount%20from%201%20to%2030000%3C%2FP%3E%3CP%3Etype%20first%20four%20digits%20and%20hit%20enter%3C%2FP%3E%3CP%3Eformatted%20value%20appears%20to%20active%20cell%20and%20cell%20below%20will%20be%20selected%3C%2FP%3E%3CP%3Ethis%20happens%20as%20long%20as%20counter%20reach%20the%20amount%20of%20numbers%20you%20told%20it%20to%20be%20coming%20from%20you%3C%2FP%3E%3CP%3Eyou%20can%20always%20stop%20adding%20new%20numbers%20just%20hitting%20enter%20when%20macro%20asks%20new%20timevalue%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMacro%20checks%20that%20you%20have%20given%20only%20numbers%2C%20no%20more%20than%2024%20hours%2C%20no%20more%20than%2059%20minutes%3C%2FP%3E%3CP%3Eand%20exactly%20four%20digits.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20with%20attached%20workbook%20or%20any%20other%20workbook.%20You%20only%20have%20to%20open%20this%20macro%20workbook%3C%2FP%3E%3CP%3Eto%20reach%20macro%20or%20you%20can%20copy%20the%20code%20to%20your%20personal%20macro%20workbook%20if%20you%20like.%20That%20way%3C%2FP%3E%3CP%3Eit%20would%20be%20always%20available%20when%20you%20use%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1560031%22%20slang%3D%22en-US%22%3ERe%3A%20Inputting%20formated%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1560031%22%20slang%3D%22en-US%22%3EGreat%20thanks.%20For%20the%20format%20sting%20I%20used%2000%5C%3A00%20and%20that%20gave%20me%20exactly%20what%20I%20needed.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Help Please- I have a lot of time data to enter in an Excel Spreadsheet. I would like to type 0955 to then appear as 09:55 in my worksheet. I know I can use a Custom format to display the data but I need to type the colon for each entry. Is there an easy way to avoid this??

4 Replies
Highlighted
Hello, using Custom Formatting is still the best option to go with. Having to type colon for each of the entry is time consuming. Just my opinion
Highlighted
Best Response confirmed by Tips1949 (New Contributor)
Solution
right mousekey
Format cells
Custom
Type:
00 \: 00
OK

Should work without problems, if not please inform again.
If it works, please mark it as the correct answer so that other who interested to know it too ... best with thumbs up (like)


Nikolino
I know I don't know anything (Socrates)
Highlighted

@Tips1949 

 

Hi,

 

attached Excel macroenabled workbook. Open it and enable macros.

Open that worksheet where you are going to input timevalues.

Macro starts to fill values from activecell and below it when you type digits to it.

 

hit ALT + F8 and select TimeMachine - macro to run.

Macro asks you how many timevalues you want to type, answer some amount from 1 to 30000

type first four digits and hit enter

formatted value appears to active cell and cell below will be selected

this happens as long as counter reach the amount of numbers you told it to be coming from you

you can always stop adding new numbers just hitting enter when macro asks new timevalue

 

Macro checks that you have given only numbers, no more than 24 hours, no more than 59 minutes

and exactly four digits.

 

Try this with attached workbook or any other workbook. You only have to open this macro workbook

to reach macro or you can copy the code to your personal macro workbook if you like. That way

it would be always available when you use Excel.

 

 

Highlighted
Great thanks. For the format sting I used 00\:00 and that gave me exactly what I needed.