Format Cells in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1491265%22%20slang%3D%22en-US%22%3EFormat%20Cells%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1491265%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Just%20wondering%20if%20there%20is%20a%20way%20to%20format%20Excel%20cells%20in%20a%20particular%20way%3C%2FP%3E%3CP%3Elike%20for%20eg%2C%20if%20cell%20contains%20time%20as%20a%20value%2C%20typing%20like%2015%3A30%20is%20kind%20of%20cumbersome%20because%20i%20have%20to%20alternate%20between%20the%20numeric%20keypad%20and%20the%20alpha%20keypad.%20is%20there%20a%20way%20to%20format%20a%20column%20or%20row%20in%20excel%20in%20which%20i%20type%20%220330%20%22%20it%20automatically%20become%2015%3A30%20or%20it%20becomes%203%3A30%20pm%20on%20its%20own.%20that%20way%20i%20can%20save%20a%20lot%20of%20time%20when%20there%20are%20thousands%20of%20entries%20to%20be%20made%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1491265%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-1491324%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20Cells%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1491324%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F710879%22%20target%3D%22_blank%22%3E%40SamDelhi%3C%2FA%3E%2C%20the%20best%20you%20can%20do%20is%20to%20enter%20the%20numbers%20in%20a%20column%2C%20then%20convert%20those%20numbers%20to%20times%20in%20the%20next%20column%2C%20as%20shown%20in%20the%20snapshot%20below%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22NumberToTime.PNG%22%20style%3D%22width%3A%20423px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F201265i1FAD70440831ACF3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22NumberToTime.PNG%22%20alt%3D%22NumberToTime.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1491632%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20Cells%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1491632%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F710879%22%20target%3D%22_blank%22%3E%40SamDelhi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20also%20write%20a%20VBA%20event%20handler%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-vbnet%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0ADim%20hrs%20As%20Double%2C%20min%20As%20Double%2C%20time%20As%20Double%0A%20%20%20%20If%20Not%20Intersect(Target%2C%20Range(%22date%22))%20Is%20Nothing%20Then%0A%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20%20%20%20%20hrs%20%3D%20CLng(Left(Target.Value%2C%202))%20%2F%2024%0A%20%20%20%20%20%20%20%20min%20%3D%20CLng(Right(Target.Value%2C%202))%20%2F%2024%20%2F%2060%0A%20%20%20%20%20%20%20%20time%20%3D%20hrs%20%2B%20min%0A%20%20%20%20%20%20%20%20Target.Value%20%3D%20time%0A%20%20%20%20%20%20%20%20Target.NumberFormat%20%3D%20%22%5B%24-x-systime%5Dh%3Amm%3Ass%20AM%2FPM%22%0A%20%20%20%20Application.EnableEvents%20%3D%20True%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThe%20above%20should%20handle%204%20digits%20placed%20within%20a%20text-formatted%20range%20%22date%22%20but%20the%20task%20grows%20if%20you%20wish%20to%20trap%203690%20as%20an%20error%2C%20or%20handle%20830%20as%20if%20it%20were%200830%2C%20or%205%20as%200500.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi Just wondering if there is a way to format Excel cells in a particular way

like for eg, if cell contains time as a value, typing like 15:30 is kind of cumbersome because i have to alternate between the numeric keypad and the alpha keypad. is there a way to format a column or row in excel in which i type "0330 " it automatically become 15:30 or it becomes 3:30 pm on its own. that way i can save a lot of time when there are thousands of entries to be made

2 Replies

@SamDelhi, the best you can do is to enter the numbers in a column, then convert those numbers to times in the next column, as shown in the snapshot below: 

NumberToTime.PNG

@SamDelhi 

You could also write a VBA event handler

Private Sub Worksheet_Change(ByVal Target As Range)
Dim hrs As Double, min As Double, time As Double
    If Not Intersect(Target, Range("date")) Is Nothing Then
    Application.EnableEvents = False
        hrs = CLng(Left(Target.Value, 2)) / 24
        min = CLng(Right(Target.Value, 2)) / 24 / 60
        time = hrs + min
        Target.Value = time
        Target.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"
    Application.EnableEvents = True
    End If
End Sub

The above should handle 4 digits placed within a text-formatted range "date" but the task grows if you wish to trap 3690 as an error, or handle 830 as if it were 0830, or 5 as 0500.