Time Entry

A simple problem I would appreciate your help with.

I have a work sheet with several cells where time is to be entered in the
format hh:mm. Unfortunately a lot of users are enetering it in the format
hh.mm.
Is there any way I can automatically convert to the right format, or not
accept the (.) but only (:).

Thanks for your help.


0
6/12/2005 8:21:58 AM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
678 Views

Similar Articles

[PageSpeed] 17

Private Sub Worksheet_Change(ByVal Target As Range)

If InStr(1, Target.Text, ":", vbTextCompare) < 1 Then
MsgBox "invalid entry"
End If

End Sub


Enter the above code in the sheets module



Mangesh


-- 
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10470
View this thread: http://www.excelforum.com/showthread.php?threadid=378378

0
6/12/2005 9:22:58 AM
Reply:

Similar Artilces:

Manufacturing Order Entry BOM Revision
Here is the situation, Our BOM's have a revision, for example 603-001 when I change a MO from Open to Released. We update the BOM Revision to 603-002. I enter a component transaction entry pick document, and the then look up the MO on the MO Entry window and the Revision has updated to 603-002 (new revision) however the picklist is still the 603-001 (which is what I want) How come the MO entry window shows the most current BOM revision instead of the revision the MO was released at? -- Doug GP Manufacturing records the revision number but does not really support r...

outlook2003: can't find MaxFileSize registry entry
Hi! all, One of the users whom I support has reached the size limit for her .pst file. The KB #832925 tell me how to change the size limit but I was unable to find all entrys in the registry. I'm can't find 11.0 under HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\ in her windows or any of the other's that I support, and all them PC are windows xp sp2 + office 2003. Any suggestions? Thanks a lot! The instructions are not clear, you must create the key and values manually. For example if you expanded Microsoft and did not see an Office key, you would select/highlig...

Dates and times
I have 2 columns labeled Date and Time. I used the format mm/dd/yyyy and found that 3/14 gives me 03/14/2003. So far so good. But I want excel to enter the slashes for me. I suspect a custom format would do this, but Excel is not very specific as to how to set one up. Similarly, for times. I want them on a 24-hr clock and found a format for this but whwn I enter a time all I get is "00:00" [to say nothing of a date in the formula bar which is a) not the correct date; and b) not needed in the timee column anyway]. I assume this has something to do with Excel assuming ...

backup time to live?
Hello, i recently did a restore on my test server to bring it back to defaults and now none of my virtual machines will connect to it saying the domain is not found, easy fix but I was just wondering if there is a time bomb or something along the lines of it. this happened with both image based backup and ntbackup. ...

Birthday Calendar Time
Everytime I add a birthday date on the Contact list, it will automatically add the date in the Calendar list. However, it seems the start time always show 12.30am and ends at 12.30am the day after the birthday. To correctly reflect the birthday start and end time, I always have to go to the Calendar list, open the birthday, click on "Recurrence" then "Remove Recurrence" and manually input the date. Is there anytime wrong with my settings? My computer time, zone, and Outlook Calendar Time Zone are all reflected correctly set. Thank you. What type of emai...

Posted this several times, Could someone please help??
Hi, I have been receiving an intermitted error message when I open up an email within Outlook 2000. At the time when I receive this message I do not have many windows open. This is the Error I get: Can't open this item. The text formatting command is not available. It may not be installed correctly. Please reinstall Outlook. Out of memory or resources. Please close all windows. My computer is running Outlook 2000 and it has the current updates installed on it. I have boosted the paging files. In the International Options under Mail format I changed both the send this en...

Real time failover
Hello, We are looking in to a failover solution, and are leaning away from going to Microsoft Clustering, because we are already up and running on a windows 2003 standard network with SQL 2000, and we do not want to re-make our entire structure. Does anyone have any recommendations? We would like disaster failover, preferably automated. Does anyone have any experience with any of the third party utilities that accomplish this? Thank you for your time. ...

Viewing ALL text entry for a cell
I have a spreadsheet which has a cell with long written descriptions for item entries in each row. Some descriptions are pretty lengthy and the cell seems to cut off the text entry once it's reached a particular length. How can I have the cell display the full text entry? I've got the cell formatted to wrap text and seems to do fine, until you reach a certain lenth (character count)??? Is there a max character count in the cells? If so, can that be changed?? I am using Excel 2000. --- Message posted from http://www.ExcelForum.com/ from Excel XP help - Length of cell contents...

Move between records on a form (skipping subform entries)
Is there a way to step through specific records on a form, instead of every record, when a subform is attached (a one-to-many relationship)? I have a form (A) and a subform (B). Form/table A contains personal information and subform/table B contains the vehicles that the individual owns. The tables have a relationship of one-to-many (form/table A has one value that links to multiple values in form/table B). I have created a form that shows the table A "one" information at the top of the screen and the table/subform B "many" information below it. I would like t...

How do I time/date stamp an entry in a note from a calendar entry?
Using Outlook 2003. I have calendar entry "Call so and so...." I have called this person before and want to time date this call above the other one. I can enter it manually however other programs I have used wil do this automatically. Surely Outlook can do this? in outlook 2003 you need to use a utility or enter it manually. You can also use VBA to do it. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.out...

Display records one time only
Primary Key is ID and data is extracted by ClubID. Fields are ID, ClubID, StpMail, FNm, LNm, MI, AD1, CO, Cty, ST, ZP. Prompt asks for up to 5 ClubID's, but the resulting Dataset has duplicates (one ID may have been a member of all ClubID's) ClubID is a year value (i.e., 2007). How do I restrain the data so I don't end-up with Duplicate values? Thanks in advance for any suggestions. -- FirstVette52 No duplicate ID's regardless of the ClubID. -- FirstVette52 "FirstVette52" wrote: > Primary Key is ID and data is extracted by ClubID. Fi...

exchange rate on journal entry #2
I ran into a strange situation today. My exchange rates are defined to pick the next exchange rate if the rates for the transaction dates are not defined. I have exchange rate defined as 1.1 for the period of 1/1/06 with expiration date of 1/7/06 and 1.2 for 1/8/06 with expiration date of 1/14/06. When I enter a journal entry for the date of 1/2/06, I expect the rate to be picked up will be 1.1 but the rate picked up is 1.2. If I enter a journal entry for the date of 1/1/06, the rate picked up is 1.1. can anyone explain? ...

Deleting multiple calendar entries
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop I tried mobile me to sync entourage with my iphone. <br> Everytime the phone and entourage synced a duplicate entry was made in my entourage calendar. How can I delete multiple events in entourage at the same time? On 1/4/10 3:37 PM, numacguy@officeformac.com wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: > Intel Email Client: pop I tried mobile me to sync entourage with my iphone. > Everytime the phone and entourage synced a duplicate ...

how do I add times in Excel and result in hours & mins
I want to insert a time when I start work and a time when I take a break, then a time when I leave work. Following that I want to be able to add up the amount of hours that I have worked. This will enable me to plan my week ahead and ensure I only allocate a specific amount of time to a project. http://www.cpearson.com/excel/datetime.htm#WorkHours -- Kind Regards, Niek Otten Microsoft MVP - Excel "Rty Shaw" <Rty Shaw@discussions.microsoft.com> wrote in message news:37D03D72-5525-4D6E-8ED7-2911B16248B0@microsoft.com... >I want to insert a time when I start work and...

Activities Date and Time
Hi, I read somewhere a while back that there was a way to customize the Activities List view to display the date and time it was created. Something about putting it into the subject field somehow, or something like that? Could someone please let me know how to do this as we are converting thousands of activities from Goldmine and as the Activities Forms cannot be modified, it is hard to find a specific activity by a date. Once a historical activity is opened the Due Date was the Create date for our migration as was the time, but is does not show in the list. We could add that to the...

sends the same email several times
my recipients are getting several copies of the same email I send Help "PJ" <PJ@discussions.microsoft.com> wrote in message news:B9C90285-B85C-4FEF-BDCE-A267A9428735@microsoft.com... > my recipients are getting several copies of the same email I send Help Are the emails normal small mail or do they have large attachments? If the former then check that you do not have your account set up more than once, Tools Menu>Accounts. If the later then is WM set to break apart messages larger than a certain size? Check for this on the advanced tab of your...

Run time error 91 #2
Has anyone else experienced this problem. At the end of a transaction we will get a message 'run time error 91 - object variable or with block variable not set' - the program freezes and we have to exit and log back into POS. The transaction posts okay so we don't lose any data but it is annoying. We have tried to find the logic thread - ie time of day, customer, product, terminal - but there seems to be no common theme. We are running SO and HQ version 2.0.2.0115. Thanks -- Merle Check your receipt template..... "Island Blue" <IslandBlue@discussions.micr...

How do i have a check display with any data entry in a cell
I am creating a simple spreadsheet for teachers and want to have only a check mark displayed regardless of what they type into a cell. Insert the check mark symbol you want from the Insert-Symbol menus int a reference cell for your formula. Then your formula in B2 would be. =IF(A2>0,$H$2,"") A2 is where the data is entered by the teachers, $H$2 is where yo inserted the check mark symbol for your reference. You can then dra this down for each row of data. Cheers, Stev -- Steve ----------------------------------------------------------------------- SteveG's Profile: ...

tricky data entry question
I need to limit a cell's entries to numbers, commas and dashes, so entries such as 1,2,3,4-10,17 could be entered (these are just examples, the actual figures can go up to 999999). The tricky part is that I would also like to only allow an entry in this cell if another cell on the sheet is not empty. An example to summarise: I want to be able to say "allow an entry in this cell if cell A1 is not empty, and only allow the characters to be numbers, dashes or commas." Of course it may not be data validation that I need, (maybe a combination of number formatting and data vlidati...

Total number of Jan 2007 entries in a list of date formatted field
I have a report that captures work done by sales reps. For each entry there exists a date. This report is exported in Excel format and the date is captured as mm/dd/yyyy hh:mm. I would like to have a column that lists the number of work entries by Jan 2007, Feb 2007, Mar 2007, etc. I am at a loss as to how I can conditionally count the occurrence based on the month and the year in this date field. Thanks in advance. =SUMPRODUCT(--(YEAR(B2:B200)=2007),--(MONTH(B2:B200)=1)) where B2:B200 holds the dates you can expand on that if you want to count for an individual =SUMPRODUCT(--(A...

Populate Markdown field in Sales Transaction Entry with differenc.
When using Extended Pricing, we would like the system to automatically populate the Markdown field on the line item with the difference between the Item’s Net Price and promotion. For example, if the Net Price of an Item is $150 and the promotion is $10 off, we would like the Unit Price to be populated with $150, the Markdown field to be populated with $10.00, and the Extended Price to be populated with $140. Not sure if you'll see this or not but I thought you (or someone) might like to know that I have gotten this request a couple times and have written such a product. Email mbs...

Counting time ranges
I have a spreadsheet that looks like this... 07:51:4 07:56:2 07:59:5 08:36:0 08:36:2 09:04:2 09:08:1 09:12:2 09:13:3 09:15:4 09:17:3 09:17:3 09:20:1 09:23:1 09:24:1 I need to be able to count how many times each hour shows up, I tried using count if but the range isnt working? Please help!!! Cathy Hi maybe something like =SUMPRODUCT(--(A1:a100>=9/24),--(A1:A100<10/24)) to count the numer of 9 hour occurences >-----Original Message----- >I have a spreadsheet that looks like this.... >07:51:40 >07:56:27 >07:59:59 >08:36:01 >08:36:24 >09:04:25 >09:08:18 >09:...

Adding trx desc from Payables Transaction Entry window to check
Hi all, My customer is trying to print check from the Payables Transaction window. But she needs to extract the transaction description onto the check. I checked in KnowledgeBase - it is possible to add if the invoice is posted. But this check is printed on the fly and so the transaction is in PM Transaction WORK table. Any idea how I can add/link this table to the PM Payment WORK table? Thanks in advance. Regards, Saras ...

Convert Date To Time Back To Date
Hi, I know the subject line is too confusing. I didnt know how to explain this so I used what I want. I had some one do some data entry work for me sometime back. Now this guy, used his own brains at added time in the format of MM:DD:YYYY instead of MM/DD/YYYY. Now once that is done, as the field was set to store date, it converted the MM:DD:YYYY to some date, other than the actual one. So I now I have completely new date with me. Now I need to take it back to original date. And I have no clue on how to do it. e.g. Actual Date - 04/08/06 Entered As - 04:08:06 Visible As - 12/30/1899 No...

how many times ... identical email to 150 contacts
Hi, I would like to know how many times an email gets sent out of exchange 2000 (via our link 512kb - ADSL) if you send 150 emails with an attachment of (2MB). I am ending 1 identical email message + identical (2mb) attachents to 150 contacts. My question is will 150 X 2MB (300 MB) get sent out through our pipe or does it function like the single instance message storage (if yes) does this have to be enabled? Please note that most of the emails that get sent out are to contacts who are not a user on there (their mailbox isn't on this server). Irene In the last exciting episode, &qu...