Input Time Without Colon

Oct 25, 2007

I 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

View 9 Replies


ADVERTISEMENT

Format Cells So 24 Hour Time May Be Input Without Colon

Nov 30, 2003

Is 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 Related

Delete String Before Last Colon If No Text After Last Colon

Nov 29, 2012

I 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] .....

View 2 Replies View Related

Quick Time Formatting - How To Get Colon In The Middle

Nov 8, 2012

ColB is a time of day in 4 digits ****

I need it to be **:**

How do I get the colon in the middle?

View 1 Replies View Related

Time Format Why I Enter Colon Between The Hours And Minutes

Oct 28, 2007

I 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 Related

Adding A Colon ( : ) To Convert A Standard Number Into A Time Format

May 6, 2009

Is 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 Related

Time Formula: Input The Arrival Time Of A Car

Feb 18, 2007

when i input the arrival time of A Car, the time he arrives late appears in column D it should read 15 Minutes. how i can get this time to appear automatic after i have registered the arrival time. A Car due at 19:00 arrived at 19:15. 15minutes late. I would be grateful if you could lead me in the right direction.............

View 3 Replies View Related

Date / Time Input On Every Row

Nov 3, 2008

I am running excel 2003. I am creating a book that needs date & time input on every row. I am trying to input this automatically based on adjacent cell being >0. Every time there is a new input, the previous dates / times update to the latest one.

View 5 Replies View Related

Input The Exact Time

Oct 25, 2007

I have a sticky problem. I have a spreadsheet set up that counts almost everything I do at work, and I would like to input the exact time I receive a call and have the spreadsheet break it down into time periods, e.g., 8-10, 10-12, 12-2, etc., to count what time of day I get most of my calls and sales. In other words, I want the sheet to read the time I input and count it as part of a specific time period, and then count the number of calls taken and sales made from those calls. I can easily compute my closing percentage when I get those numbers. Also, since I have all the days of the month on the same spreadsheet, would it be possible to break them down by day (range)?

I can see where knowing how to do this could come in handy for other projects as well.

no VBA. I play with it at home, but I also struggle with it mightily.

View 9 Replies View Related

Adding A Colon

Dec 30, 2009

I 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.

View 10 Replies View Related

Time Fractions And User Input

Mar 12, 2008

What I am doing is creating a spreadsheet solution to tack race times. These show up in past performances in the following formats in paper copies: 45 2/5 seconds. The end user would have to put in maybe 30 of these times based on the number of contestants. Keeping in mind that the 45 2/5 number will be used in some calculations, I want to give the user the easiest input interface. Meaning instead of inputting like this; 0:45.4 (mm:ss/00 format), I would like them to just enter 45.4. the spreadsheet would input format this cell into a time format that would be able to have calculations done to it. Another example might be 1:35.60 (mm:ss.00 format) which is 1 minute 35 and 3/5 seconds. This would be entered as 135.6.

A few questions first:

1) Would it be easier to leave the number inputed alone until the calculations ar finished then translate into a time factor?

or

2) would it be necessary to convert into a time factor as inputted be cause of the 60 factor involved with the time factor?

Having asked those questions, setting this all up with the exact scope I am working towards, any help or sugesstions as to the best way to proceed would be fantastic!

View 9 Replies View Related

Vba - Input Filename To Open Each Time

May 13, 2007

Below is the code that references a file to import = Jan.txt.
There are different files I want to import, using this macro.

However I have not been successful with creating a variable
and then creating a statement which requires me to input the filename that I want to import for the macro run.

With ActiveSheet.QueryTables.Add(Connection:="TEXT;U:Jan.txt", Destination _
:=Range("A1"))

View 6 Replies View Related

Entering Times Without A Colon

Oct 20, 2009

I 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.

View 12 Replies View Related

Start Colon To Be Visible

Aug 21, 2009

I 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.

View 27 Replies View Related

Period Replaced By Colon

Oct 3, 2006

Replaced 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 Related

Input Future Date And Time Into One Cell

Oct 20, 2012

I need to input a future date and time into one cell. For example, if I have a member ship that ends on October 20th at 3:00 pm exactly the kind of format you get when you enter the function "NOW". I would like to put that into one cell and then be able to add and subtract from it.

For example I would like to do something like this, if I had inputed the date and time in A1:

"=Now-A1", it would be cool if I can get the remaining hours or minutes along with how many days are left but not completely needed.

It would also be beneficial if I can drill down further and do it by time zone as well.

View 2 Replies View Related

Auto Date And Time - Input Data

Dec 6, 2013

Auto date and time using excel macro.

In cell B2 until Z2 I am inputting data.

Once the data has been inputted in cell B2, I like to have date and time to have in Cell B6.

If input in cell C2 then cell C6 to have date and time..

View 4 Replies View Related

How To Lock Down Time And Date When Input Data

May 23, 2012

Is there anyway to lock down the time & date when I input a data? Assuming I have a TIME formula in column A, and a DATE formula in column B. So when I input any data in column C, A & B can automatically show and also lock the time & date.

View 5 Replies View Related

System Name And Time Update Based On Input?

Feb 26, 2013

In Excel sheet1 Column G has Names(John,Augutus,Jennie ect..) In Coulmn H i need to daily update the Present or Absent status, once i update the status i need Coulmn I to automatically fetch the System name and Column J should update with Time and date..

View 7 Replies View Related

How To Automatically Lock Cells Every Time Input A Value

May 6, 2004

a code that would automatically lock a cell every time I input a value to it?

View 2 Replies View Related

Input Current Date Time When Saving

Aug 26, 2006

I am trying to create a code so that when the save button is hit, it puts the current date and time into a given cell before it saves. This way I will know the last time something was updated and saved.

View 9 Replies View Related

Lock User Input For A Given Amount Of Time

Jan 8, 2008

Is there a way to freeze the user input when it first comes up for a given amount of time. In essence, I am trying to force the user to read before clicking.

View 9 Replies View Related

Trim Formula: Separated By A Colon

Oct 23, 2008

I 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?

View 5 Replies View Related

Format Cell To Show Colon

Jan 12, 2006

Is 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

View 12 Replies View Related

Removing Colon From Multiple Cells?

Jul 7, 2014

I 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 Related

Check User Input To Prevent Run-Time Error

Dec 28, 2007

what I want. It relies on an input box asking the user to enter a part code, which when found creates a duplicate copy. However, if the user enters a part that doesn't exist I get a run-time error. Can anybody point me in the right direction so when a user enters an incorrect part they get a message telling them it doesn't exist.

Private Sub SortTest_Click()
Dim s As String
Dim r As Excel. Range

Range("A2").Activate
s = InputBox("Enter the number you wish to find")
If StrPtr(s) = 0 Then
MsgBox "You must enter an existing part number!"
Else
Set r = Cells.Find(What:=s, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Range(Cells(r.Row, "A"), Cells(r.Row, "AH")).Copy
Sheets("APL").Cells(r.Row, "A").Insert Shift:=xlDown


Application.CutCopyMode = False
Application.Goto Sheets("APL").Cells(r.Row, "H")
Selection.Offset(-1, -5).ClearContents
Selection.Offset(-1, 0).Select
End If

End Sub

View 7 Replies View Related

Sort Names Separated By Semi-colon?

Jan 24, 2013

I have a long list of names

Doe, John;Doe Jane; etc..

Is there any way to have Excel separate them into individual cells?

View 2 Replies View Related

Input And Display Data In Cell In H:mm Format Without Changing To Time

Feb 25, 2010

Can i put into the cells B38-AF38, that would display the sum of B20:AF20 + B24:AF24 + B28:AF28 + B32:AF32 +B36:AF36 in a h:mm format.

When imputing the data into a cell in a h:mm format, I need it to display in that cell and formula bar the h:mm format without having to put an apostrophe in front of "h" or ":mm" value. Once complete, this form is going to go to other trainers to use on their computers and I dont think they would remember to keep putting an apostrophe before the data.

Example:

B20= 1:15 (1h15m) NOT 1:15:00 AM

B24= :30 (30min)

B28= :45 (45min)

B32= 1:45 (1h45m) NOT 1:45:00 AM

B38= 4:15 (4h15min)

I have attached a copy of the form for better clarification.

It doesnt seem to be complicated, but it's certainly a lot more than I thought.

View 4 Replies View Related

Search Timestamp Value Giving Input With Date And Time Rounded Off Value Alone

Jul 29, 2013

I am a beginner to Excel macros. I am writing a macro to find the value based on the search criteria am giving

E.g. ( Record in table)

Column A
24/07/2013 15:04:05

Input am giving

24/07/2013 15:04 ( since am not sure about the milliseconds)

I need to find and fetch the Datas based on the input am giving

View 2 Replies View Related

Concatenate With Semi-colon Across Multiple Cells In Excel

Mar 4, 2012

What 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.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved