Quick Time Formatting - How To Get Colon In The Middle
Nov 8, 2012ColB is a time of day in 4 digits ****
I need it to be **:**
How do I get the colon in the middle?
ADVERTISEMENT
Input Time Without Colon
Oct 25, 2007I am making a template for our security kiosk log and I wanted to simplify it as much as possible. I found the "time mask entry" and imputed that into the vba code. It was working for awhile however stopped working a few days ago. I went back and copied and pasted it again and changed the range to what I needed and an error came up stating "unable to set the NumberFormat property to the range class."
Private Sub Worksheet_Change(ByVal Target As Range)
Dim vVal
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub
With Target
vVal = Format(.Value, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
Application.EnableEvents = False
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm"
End If
End With
Application.EnableEvents = True
End Sub
Delete String Before Last Colon If No Text After Last Colon
Nov 29, 2012I found the following formula on another site that deletes all the text before the last colon (it deletes the colon as well)
=TRIM(RIGHT(M2,LEN(M2)-SEARCH("@#",SUBSTITUTE(M2,":","@#",LEN(M2)-LEN(SUBSTITUTE(M2,":",""))))))
Is it possible to modify it so that it only triggers if there is nothing after the last colon?
As you can see in the example below I have some notes entered by users and I would like to delete the ones where they didn't actually enter a note after the last colon:
11/05/2012 13:19:53 NEW MIKE : cld poe male sd cu not in till 3pm
10/18/2012 08:03:55 NEW HOSSEN : called no answer
10/15/2012 11:15:41 NEW HOSSEN :
10/12/2012 08:00:26 NEW HOSSEN :
10/11/2012 12:17:14 NEW HOSSEN : called no answer
11/14/2012 13:42:07 ACT MIKE : cld poe spk to cust sd cm back at 330
I would like to be left with just this instead:
11/05/2012 13:19:53 NEW MIKE : cld poe male sd cu not in till 3pm
10/18/2012 08:03:55 NEW HOSSEN : called no answer
10/11/2012 12:17:14 NEW HOSSEN : called no answer
11/14/2012 13:42:07 ACT MIKE : cld poe spk to cust sd cm back at 330
Link to article I found this formula in : [URL] .....
Time Format Why I Enter Colon Between The Hours And Minutes
Oct 28, 2007I have used the format [hh]:mm in a cell for 24 hr clock calculations. Why do I have to enter the numbers with a colon when I populate the cells? Is there a way to set it up so I just type in the four numbers and the colon between the hours and minutes populates itself?
View 9 Replies View RelatedFormat Cells So 24 Hour Time May Be Input Without Colon
Nov 30, 2003Is there a way to format cells so 24 hour time may be input without the colon, as an example 1425 instead or 14:25? We can do it in Access. One would think it possible in Excel, yet I have not discover how if indeed its possible.
View 9 Replies View RelatedAdding A Colon ( : ) To Convert A Standard Number Into A Time Format
May 6, 2009Is there a way to enter a colon into a standard number to create a value that can be formatted into a 24 hour time value? eg a time is listed as 1345 with a general number format, and I want it returned as 13:45 witha custom format of hh:mm. Other than creating a table and using a vlookup function
View 3 Replies View RelatedAdding A Colon
Dec 30, 2009I am a basic excel user and what I want is to be able to add in a time and have the colon automatically entered for me. For example, I want to add 130 and have it come out to 1:30, or 1215 and have it come out to 12:15. Seems easy to me but I cannot figure it out.
Entering Times Without A Colon
Oct 20, 2009I need to find a way to enter times into excel without having to use a colon.
The data I need in the cell is the hour, the minute, and either AM or PM. No seconds, which I believe is why Chip Pearson’s time VBA procedure doesn’t work for me, and I don’t know enough about VBA to change it for my needs.
So for example, I need to enter in times like 10:09 PM or 12:40 AM or 2:33 AM.
All my times will be entered into Column D.
I have done a ton of searching and tried several different solutions, but none of them allow me to put in AM or PM. If I just put 1009 into a cell with Chip Pearson’s macro, it will display as 10:09 AM, which is great if I need it to be AM. But if I need it to be PM, and I enter in “1009 p”, it will display as “1009 p”, neglecting the colon and not displaying the time correctly.
Does anyone have a solution where I can type in a 3 or 4 digit number (233 or 1240) and either “a” or “p” after it, and have it display as 2:33 PM or 12:40 AM?
I use Excel 2007, if it matters.
Start Colon To Be Visible
Aug 21, 2009I downloaded some data from an extarnal source to excel... the texts I get appear as 'TEXT' (mind the colons at start and end of TEXT).
However, in some of the cells the Start Colon is visible and in some cells it isn't (although it's there and can be viewed in the formula bar).
I want the start colon to be visible.
Period Replaced By Colon
Oct 3, 2006Replaced the period with a colon, 8.23 resulted in 8:23. I have since deleted the Add In. Now every time I enter a "." it is replaced by a ":". This happens even when I disable macros on the spreadsheet. I cannot see any VBA code.
View 2 Replies View RelatedTrim Formula: Separated By A Colon
Oct 23, 2008I have been using the following formula's to trim some information that's separated by a colon, such as Pioneer Clinic:Dave McAfee...so that I have a column with Pioneer Clinic and a column with Dave McAfee:
=LEFT(G5,FIND(":",G5)-1)
=TRIM(RIGHT(G5,LEN(G5)-FIND(":",G5)))
However, now some of my data is separated by two colons! AVM:Pioneer Clinic:Dave McAfee. the formula for separating this so I still have two columns of Pioneer Clinic and Dave McAfee?
Format Cell To Show Colon
Jan 12, 2006Is there any way that I can format a cell to insert a colon three spaces to
the left from the end of any group of numbers that I type in the a cell?
Examples 9:15 10:15
Removing Colon From Multiple Cells?
Jul 7, 2014I have been sent a spreadsheet that was populated from another system. I need to run some VLOOKUPS but the cell that I need to compare has a ' in front of the text/number eg 'VB123456. I have found a thread on how to remove it for a number but it does not work with letters.
View 6 Replies View RelatedSort Names Separated By Semi-colon?
Jan 24, 2013I have a long list of names
Doe, John;Doe Jane; etc..
Is there any way to have Excel separate them into individual cells?
Concatenate With Semi-colon Across Multiple Cells In Excel
Mar 4, 2012What is the best way to concatenate with a semi-colon across multiple cells in excel. The one kicker is I only want to concatenate where the cell is populated.
If I write a simple Concatenate or combined columns, if a cell is blank I get the multiple.
Excel 2010 :: Unable To Recognize Cell That Contain Colon Correctly
Oct 4, 2011i have encountered a problem which happens when you write data that contain : in an excel sheet (i use excel 2010)
for instance if i enter to one of the cell 45:58 excel sees it as 01/01/1900 21:48:00 when i try to get the information by using a function i will get the wrong data for example typing in the different cell LEFT(Cell,5) will result 1.908
(i receiving the data from an outside source in this way and i need to make analysis)
i have noticed that the first 2 digits (21 in the example) are related to the number i have choosen in a 24 hour cycle for instance
24:58 will result 01/01/1900 00:58:00
26:58 will result 01/01/1900 02:58:00
48:58 will result 02/01/1900 00:58:00
Excel 2010 :: VBA Code To Save File As Semi Colon Delimited Format
Jun 3, 2014I am looking for VBA code that will save Excel 2010 files in semi-colon delimited format without having to chage the universal language options.
View 1 Replies View RelatedQuick Way To Add Tabs
Feb 5, 2009if there is a quick way to add any number of tabs to a spreadsheet.
ie one particular spreadsheet i need to have 32 sheets (tabs) but am getting seriously bored to right click insert worksheet time after time. there must be a quicker way?
[VBA] Quick Copy / Paste
Jun 4, 2008The goal of this code is to check between two sheets, if it finds a match between both sheets (somewhere in column A) then it should copy E,F,G columns from Sheet2 to Sheet1..
The Sub Test()
aLastRow = ActiveSheet.UsedRange.Rows.Count ' last row of active worksheet; assume master is active sheet .....
Recursion (Quick Sort)
Jul 31, 2008I've been having a play with sorting integers in A1:I1 (because that was the example in Wikipedia). I've got some code that kind of works, but the recursion is entirely manual.
(I know it's only a mickey mouse example, and no practical use!)
Sub quicksort()
Dim iPivotIndex As Integer, iPivotValue As Integer, iTempValue As Integer, iStoreIndex As Integer, i As Integer
Dim iStartNumber As Integer, iEndNumber As Integer
'how do I give it the original iStartNumber and iEndNumber
iStartNumber = InputBox("Start")
iEndNumber = InputBox("end")........................................
Quick Auto-Numbering
May 4, 2009Auto-Numbering
just an example:-
56
57
58
59
60
The Column above is the first column on a selected sheet.
i will select 56 and from there (End-Shift+Down arrow) which selects all the values from 56-60...
My question is from here on if there is a shortcut key or 'vba macro' that can autonumber from 1.
Thus giving output result of..
1
2
3
4
5
i want to record the solution for above problem in a macro recorder for different numbers that is why i have to do (End-Shift+Down arrow)
Creating A Quick Label
Oct 13, 2009I am doing a "Lost and found" spreadsheet project for a hotel.
Each row contains the lost items with all the necessary info in separate cells (Room nr, found by, reference nr, client name, etc)
Every item is being bagged and then a label is put on it. ( has the same information but its just arranged prperly. (Like a label )
What I would like to do is to have a column in each row that has a "print" button. What would happen is that the macro would create a label in sheet2, print it on the default printer and then erase everything on sheet2. (I am kind of new to macros so I dont even know if I need to use the sheet 2 for it).
I have already done a kind of a template for the label and set it up on sheet2. I would probably manage to get the print function working with the online tutorials(if not i'll be back) but can't figure out how to do the first bit of it.
Quick Replace On Many Tabs
Feb 16, 2010I have two spreadsheets, each with about 50-60 separate worksheets in them, and each spreadsheet has simarly named tabs. I am trying to replace the values in column Q on spreadsheet 2, and move them to column O on spreadsheet 1, on each worksheet.
Is there a way to do this quickly, or will I have to copy-paste many times over?
Formatting With Time Calculation
Oct 15, 2009I have a calculation whereby I use two variables which are 1. Man hours left in the week formatted as [hh] and 2. Amount of work left to do in the week formatted as 0" Man Hrs" I need to carry out a calc which says:- Man hours left in the week - Amount of work left to do in the week.
The answer should tell me if I have a deficit or surplus of hours which can then use for planning purposes. Unfortunately the problem I have is that I can't make the formatting the same for both unless someone knows of a workaround.
Man hours left in the week = 128. Man hours required to complete all work this week = 224. Therefore I need to say 128-224 = -96. However when I do this this an answer of 16 is returned.
Conditional Formatting With Time Value?
Jul 16, 2013I have an Excel report I have to generate. I need "Call Back" (F3) to show highlighted in red if time value of (F3) is = to or greater than 4 hrs. of "Time Reported" (C3) and highlighted in green if value is before 4 hrs. Also "Arrival Time" (G3) needs to be highlighted green if within 24 hrs. of "Time Reported" (C3) and highlighted red if after 24 hrs. of "Time Reported" (C3).
View 7 Replies View RelatedAuto Time Formatting
May 28, 2009Hears a quick question..
Is there away in VB that a cell can be automatically set to time
What i mean at the moment when i need to type in a time i have to type it in as follows 10:00 ( the cell is formatted to time)
What i was wondering is there a way of programming in VB so that when the user types in 1645 it automatically changes it to 16:45
Calculating & Formatting Time..
Jun 2, 2009I am trying to track how long a macro takes to run from start to end.
View 4 Replies View RelatedTime & Date Formatting
Sep 18, 2009i have a cell with a date in it, e.g. 18/09/09 is there anyway that i can ask excel 2007 to look at the cell and simply return the month into an adjacent cell, so in this case September.
A1 B1
Date Month
18/09/09 September
Winzip - Quick Way To Zip Up Files From Different Path
Aug 27, 2009I have a folder that contain a list of files in shortcut type. So that i can just click it without going to different drives and paths to open those files and it really save my time.
After updating those files, i need to zip it up. There are many files to zip up and it takes time by going to its path one by one. Do you guys know is there any method to zip up those files without going to its relevant drives and paths?
Quick CSE Array Formula Entry
Feb 11, 2012I wonder if there is a way to enter my formulas as an array formula using Control-Shift-Enter in a more time saving way than one line by one line
I have 600 rows of array formulas all in column E but at different intervals with some blank rows inbetween
The formulas are in place but just need the CSE to enable the array formula { }
I am not looking forward to line by line entry.