Cutom Time Format

I want to format a cell with a custom time format that will allow my users to 
type in the time without the colons.  [1014 = 10:14].  I've tried several 
different ways of setting up a custom format to do this, but nothing works. 
Anybody have any ideas?

Thanks!
-- 
Sherry
0
Ladypep (3)
8/18/2005 1:32:10 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
327 Views

Similar Articles

[PageSpeed] 25

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

    On Error GoTo ws_exit:
    Application.EnableEvents = False
    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
        With Target
            .Value = TimeSerial(Int(.Value / 100), _
                    .Value - Int(.Value / 100) * 100, 0)
                    .NumberFormat = "HH:MM"
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Ladypep" <Ladypep@discussions.microsoft.com> wrote in message
news:AB90000B-CCA8-46A9-923D-9773C69EB5DD@microsoft.com...
> I want to format a cell with a custom time format that will allow my users
to
> type in the time without the colons.  [1014 = 10:14].  I've tried several
> different ways of setting up a custom format to do this, but nothing
works.
> Anybody have any ideas?
>
> Thanks!
> -- 
> Sherry


0
bob.phillips1 (6510)
8/18/2005 1:39:11 PM
Thanks, Bob, for your help.  This works great, except I need for the date 
format to be hours and minutes only, and with no a.m. or p.m. Users will 
choose a.m. or p.m. from another field, so if they type in 214 it should read 
2:14 whether it's a.m. or p.m.  It works if I use military time to indicate 
p.m.s but I need it to not be either one.  I know virtually nothing about 
visual basic, so I don't know how to edit the code to make that work. Is it 
possible?

Thanks
-- 
Sherry


"Bob Phillips" wrote:

> Private Sub Worksheet_Change(ByVal Target As Range)
> Const WS_RANGE As String = "H1:H10"
> 
>     On Error GoTo ws_exit:
>     Application.EnableEvents = False
>     If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
>         With Target
>             .Value = TimeSerial(Int(.Value / 100), _
>                     .Value - Int(.Value / 100) * 100, 0)
>                     .NumberFormat = "HH:MM"
>         End With
>     End If
> 
> ws_exit:
>     Application.EnableEvents = True
> End Sub
> 
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
> 
> 
> 
> -- 
> 
> HTH
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> 
> "Ladypep" <Ladypep@discussions.microsoft.com> wrote in message
> news:AB90000B-CCA8-46A9-923D-9773C69EB5DD@microsoft.com...
> > I want to format a cell with a custom time format that will allow my users
> to
> > type in the time without the colons.  [1014 = 10:14].  I've tried several
> > different ways of setting up a custom format to do this, but nothing
> works.
> > Anybody have any ideas?
> >
> > Thanks!
> > -- 
> > Sherry
> 
> 
> 
0
Ladypep (3)
8/18/2005 2:50:11 PM
Sherry,

That shouldn't give AM/PM, it should just give a 24 hour clock. It is here,
I type 214, I get 02:14, not 2:14AM. I type 1414. I get 14:14, not 2:14PM.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Ladypep" <Ladypep@discussions.microsoft.com> wrote in message
news:AB591815-BD7B-45C2-8584-E73C9221DCEC@microsoft.com...
> Thanks, Bob, for your help.  This works great, except I need for the date
> format to be hours and minutes only, and with no a.m. or p.m. Users will
> choose a.m. or p.m. from another field, so if they type in 214 it should
read
> 2:14 whether it's a.m. or p.m.  It works if I use military time to
indicate
> p.m.s but I need it to not be either one.  I know virtually nothing about
> visual basic, so I don't know how to edit the code to make that work. Is
it
> possible?
>
> Thanks
> -- 
> Sherry
>
>
> "Bob Phillips" wrote:
>
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Const WS_RANGE As String = "H1:H10"
> >
> >     On Error GoTo ws_exit:
> >     Application.EnableEvents = False
> >     If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> >         With Target
> >             .Value = TimeSerial(Int(.Value / 100), _
> >                     .Value - Int(.Value / 100) * 100, 0)
> >                     .NumberFormat = "HH:MM"
> >         End With
> >     End If
> >
> > ws_exit:
> >     Application.EnableEvents = True
> > End Sub
> >
> > 'This is worksheet event code, which means that it needs to be
> > 'placed in the appropriate worksheet code module, not a standard
> > 'code module. To do this, right-click on the sheet tab, select
> > 'the View Code option from the menu, and paste the code in.
> >
> >
> >
> > -- 
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Ladypep" <Ladypep@discussions.microsoft.com> wrote in message
> > news:AB90000B-CCA8-46A9-923D-9773C69EB5DD@microsoft.com...
> > > I want to format a cell with a custom time format that will allow my
users
> > to
> > > type in the time without the colons.  [1014 = 10:14].  I've tried
several
> > > different ways of setting up a custom format to do this, but nothing
> > works.
> > > Anybody have any ideas?
> > >
> > > Thanks!
> > > -- 
> > > Sherry
> >
> >
> >


0
bob.phillips1 (6510)
8/18/2005 4:12:29 PM
Bob, thanks. I deleted it and pasted the code in again and it worked 
perfectly! Thanks you so much!
-- 
Sherry


"Bob Phillips" wrote:

> Sherry,
> 
> That shouldn't give AM/PM, it should just give a 24 hour clock. It is here,
> I type 214, I get 02:14, not 2:14AM. I type 1414. I get 14:14, not 2:14PM.
> 
> -- 
> 
> HTH
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> 
> "Ladypep" <Ladypep@discussions.microsoft.com> wrote in message
> news:AB591815-BD7B-45C2-8584-E73C9221DCEC@microsoft.com...
> > Thanks, Bob, for your help.  This works great, except I need for the date
> > format to be hours and minutes only, and with no a.m. or p.m. Users will
> > choose a.m. or p.m. from another field, so if they type in 214 it should
> read
> > 2:14 whether it's a.m. or p.m.  It works if I use military time to
> indicate
> > p.m.s but I need it to not be either one.  I know virtually nothing about
> > visual basic, so I don't know how to edit the code to make that work. Is
> it
> > possible?
> >
> > Thanks
> > -- 
> > Sherry
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Const WS_RANGE As String = "H1:H10"
> > >
> > >     On Error GoTo ws_exit:
> > >     Application.EnableEvents = False
> > >     If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> > >         With Target
> > >             .Value = TimeSerial(Int(.Value / 100), _
> > >                     .Value - Int(.Value / 100) * 100, 0)
> > >                     .NumberFormat = "HH:MM"
> > >         End With
> > >     End If
> > >
> > > ws_exit:
> > >     Application.EnableEvents = True
> > > End Sub
> > >
> > > 'This is worksheet event code, which means that it needs to be
> > > 'placed in the appropriate worksheet code module, not a standard
> > > 'code module. To do this, right-click on the sheet tab, select
> > > 'the View Code option from the menu, and paste the code in.
> > >
> > >
> > >
> > > -- 
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Ladypep" <Ladypep@discussions.microsoft.com> wrote in message
> > > news:AB90000B-CCA8-46A9-923D-9773C69EB5DD@microsoft.com...
> > > > I want to format a cell with a custom time format that will allow my
> users
> > > to
> > > > type in the time without the colons.  [1014 = 10:14].  I've tried
> several
> > > > different ways of setting up a custom format to do this, but nothing
> > > works.
> > > > Anybody have any ideas?
> > > >
> > > > Thanks!
> > > > -- 
> > > > Sherry
> > >
> > >
> > >
> 
> 
> 
0
Ladypep (3)
8/18/2005 5:05:02 PM
Reply:

Similar Artilces:

Outlook Express Visual C++ Runtime Library Run-Time Error
I have a friend who's using Outlook Express 6 and is getting an "abnormal program termination" error whenever he attempts to attach a graphic file within Outlook Express. He reports that it works fine when using the Explorer shell to call OE. My friend has WinXP Home. Any advice? Jay ...

Custom formatting #7
How can I do the following custom formatting – I need trailing dots before the number ……..1,234.00 Thanks for any help Right Click > Format Cells > Select Custom Enter "........ "0.00 "John Knoke" <JohnKnoke@discussions.microsoft.com> wrote in message news:586761E6-32A9-4911-BF50-3790614145B2@microsoft.com... > > How can I do the following custom formatting - > I need trailing dots before the number > ....1,234.00 > > Thanks for any help > > John, Use a custom number format of *.#,###.00 Note the period between the * and the f...

Convert time stored as decimalised number to time format
Hello How can I convert 3.5 hours to 3:30:00? -- Emma Emma A1/24 and formate as time Mike Rogers "Emma" wrote: > Hello > How can I convert 3.5 hours to 3:30:00? > -- > Emma ...

Date and time onto a form
Hi, I am designing a form that my client would like the date and time automatically recorded in a field and stored so that when they refer back it shows when the record was done. The 'NOW' function only shows the current date & time and keeps updating. Is there a way of putting the 'NOW' value into a field, then locking it so it doesn't change when the record is reopened. Hope that makes sense! Kazlou The form stores data in a table. You need to add a date/time field to your table to record the date and time a record was last updated. 1. Open the table in desig...

Keeping the format but update the data
I have a report I run everyday. I want to update the data, but keep the column sizes and formatting. Any suggestions? Anybody suggest a good ebook to become more formuliar with excel? JoeM JoeM, this could be as simple as recording a macro during the formatting of the sheet after importing the data once. Then each day, after importing the new data, you simply run that macro. As for online eBook's for Excel, I'm not actually aware of any although I suspect a Google search would turn up several. It's hard to beat the "For Dummies..." series of hardcopy...

Configuration restarts each time an Office module is opened
Hi All I am going quite mad. I have installed Microsoft Office Home and Student 2007 on one Vista notebook. Well and good. I try it on another notebook, register it successfully and now each time I open Word it tries to perform a setup again, with a Configuration Progress dialogue popping up. A few decades later Word appears. There is absolutely no indication what the problem may be. Any ideas are welcome. Cheers Andre Never mind. Problem solved. The registry needed manual editing. "J Andr� Labuschagn�" <technical@eduadmin.com> wrote in messa...

NEED HELP-wrong formatting saved ??
After using word 2007 and saving a word file, it changed the formatting from the previous file (i think it was .wps microsoft works word processor) and now its all messed up.....I think I saved it in some other wrong encoding standard......now, whenever I open this file now half of it is in some weird looking unreadable characters....like this with wha捬屨捦ㅳ尠晡‰汜牴档晜獣‰歜牥楮杮尰扤档慜㍦㔱㔰楜獮獲摩㌱ㄳ〶‷਍灜牡素筽⩜灜獮捥癬ㅬ灜畮牣屭湰瑳牡ㅴ灜楮摮湥㝴〲灜桮湡⁧屻湰硴慴ges! That is what seems to happen once the system breaks its laws from the inside. ......couldnt find online solutions...does anyone have any suggestions or s...

Conditional Formating: linking to display another cell
Hello, I am trying to make a traffic light with symbols and I've read to 'use a separate cell for the dropdown choices, with their resulting value linked into the formatted cell through an IF function, using the character that you want to display.' So: =IF someone enters '1' in B8, THEN display contents of $C$4 (will it display font and attributes?) =IF someone enters '2' in B8, THEN display contents of $C$5 =IF someone enters '3' in B8, THEN display contents of $C$6 But I've been reading everywhere and CF is very new to me and I need ...

not enough memory or disk space to display or print the picture after 1st time
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC I know this topic has been covered before, but not in the way I get the problem. I can open a document with a math formula or a graphic the FIRST time and it will correctly display. Any changes to the document or any subsequent openings I then get the error message, &quot;not enough memory or disk space to display or print the picture&quot;. <br><br>eMac 1.25 Gz 10.4.6, Word 2004 <br><br>Thanks. One possible cause: You're *way* behind on Tiger -- it's been at 10.4.11 for ...

Newbie Needs Help With Formatting E-Mails
Newbie Needs Help With Formatting E-Mails Hi all. I'm new to this site and would appreciate some formatting help. I created a new Word doc and copied and pasted some graphics and some simple text boxes in to it. The graphics and text boxes were created in Powerpoint by someone else. Looks fine on my screen. Before sending it out via e-mail to an Outlook e-mail group I have, I first sent myself a test message to see what it would look like on the receiving end. It looks a mess! The text boxes are all over the place, as is the text that is not in a box. Also, everything slide over to ...

2003 format in 2007?
I'm used to Outlook 2003, but I just bought a new computer and installed Outlook 2007. I view a week at a time, and I prefer the calendar format in 2003 -- it shows the whole week in two columns and I didn't have to scroll. Is there a way I can use the same format in 2007? No, unfortunately that view had to be removed to enabled calendar overlay. Try a two week view: http://slipstick.me/2wk -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dai...

CRM Time Error
Hi, The dates stored in the DB in sql Server is diffent, from when you browse it throghe IE. Correct dates shows in IE, but not in SQL DB. I am generating a Birthday Reminder which is not coming in the correct date since time in DB is Incorrect. Please Help..... Irshard Zahir Hi Irshard, The dates in the database are converted to the local date of the user. The problem can be tackeled by using the CRM SDK. Hopefully this works if not please let me know. Regards, Ron "Irshard" <Irshard@discussions.microsoft.com> wrote in message news:9E7A1EC9-87E7-4B8D-8F0F-B7CFE9...

formatting #2
Is the strikethrough formatting option no longer available in Publisher? Or if you need to use a strikethrough do you have to draw a line through the text? The line does not always stay in the same spot when you group and then have to paste on another page. Waggie wrote: > Is the strikethrough formatting option no longer available in > Publisher? Or if you need to use a strikethrough do you have to draw > a line through the text? The line does not always stay in the same > spot when you group and then have to paste on another page. ========================== You have the an...

Trying to get a type of video file format to open and run on my we
I created a video using Windows Movie maker and saved as .wmp file. I saved it to my computer and then inserted on my web page under Front Page 2003. Nothing! I then converted the file to avi format - still nothing! I then tried mpeg4 still nothing. The manual is next to useless on this subject. Anyone tell me what I need to do so that when a user opens up the page the video plays automatically. -- Simon Holloway Save as a .wmv and insert a suitable player into the page. For IE only, Insert->Web Component->Advanced Controls->ActiveX Control Click Next Choose Window...

Text formatted email
I am trying to email a long hyperlink path to some one that can only receive text formatted emails. When they receive the email it, the hyperlink gets converted in to 2 lines, top line is a hyperlink (path incomplete) the second line is "text" with the balance of the hyperlink path. I placed < > around the hyperlink and it still splits in to 2 lines. Is there any way to keep the full link path intact? Thanks! jvs Tiny URL to the rescue! :-) http://tinyurl.com/ -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Cr...

Import data not in correct format
Import data reads: Need to do statistical anaysis on hundreds of numbers and Cell value # of Cells don't want to enter the numbers by hand. 3 1 4 2 Would like: Cell Value 3 4 4 ...

How to stop outlook resending attachments multiple times
When I send an email with an attachment, it will send several times before I manually have to stop it. Does anybody have any suggestions on how to rectify the problem? "fisco" <fisco@discussions.microsoft.com> wrote in message news:F793C1E7-AE4D-45F7-BCA6-98C570F2F915@microsoft.com... > When I send an email with an attachment, it will send several times before > I > manually have to stop it. Does anybody have any suggestions on how to > rectify > the problem? Sorry, but this newsgroup is for questions about Access, the database product tha...

Four Conditional Formats?
Dear all, I am attempting to set up a template for someone else to use on another machine, and wish conditional format a cell such that it has four potential formats. The first is based on an adjacent cell value, and if this is met, then it is colour 1. The remaining three, are all AND statements involving the first cell and another cell value being less than date A, less than date B or less than date C. Is this possible to do? I have limited VB knowledge, and am wary of using add-ins as the people I am sending this to to use have limited excel knowledge. Regards, Steve -- stevepain -...

Conditional Format
Hi, I want to assign a number to a certain conditional format. When the condition is true it should put a 1 in the cell and when the condition is false a zero or nothing should be assigned. Anyone? I seem to make the wrong code all the time. Thanks, Johanna Use the same test that the CF uses, and return 1 for TRUE and "" for FALSE. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jootje" <Jootje@discussions.microsoft.com> wrote in message news:CB0B3201-2DD9-466A-94FF-108691C77FAF@microsoft.com... > Hi, > >...

my formats get pushed out when I insert columns
Hi folks, I am having trouble when inserting columns in a large data field. Whenever I insert a column at some point in the data field, th formulas within the data field adjust, or 'stretch', to accomodate th added column, however my conditional formatting gets pushed along t the right and doesn't adapt itself in the same way the formulas do. This means whenever I insert a column, the formulas remain correct bu I have to reset the conditional formatting for the entire data field. I would be extremely grateful for any advice on this situation. Best regards, melvi -- Message pos...

calculating quantity on hand based on date-time stamp
Hello: I am always unclear on the proper syntax for pulling date-related data. I need to pull a field in my query, below, based on the field called "IV00118.CHANGEDATE_I". This is the date in which an end user changed the cost of an item. To give you some background, the IV00102 table in my query below is the Item Quantity Master table, while the IV00118 table is the Item Cost Change History table. The field that I want to pull based on that date field is the IV00102.QTYONHND field. This field is the inventory quantity on hand field. How would I format the syntax bel...

Dynamics CRM 4.0
Hi, I've modified the format in the system settings screen to be "English (United Kingdom)", however all dates are still appearing in US format. Even when you expand the calendar control it displays incorrectly. Numeric values are correct - showing the pound sign and correct decimal point. I've restarted IIS, made sure all SQL logins are British English and the default regional settings of the server are set to British English. Even rebooted the server but this still hasn't corrected the problem. Can anybody tell me how to resolve this please Thanks I was looking ...

Custom Formatting numbers question
I have a spreadsheet with lot of millions numbers floating around. When I make graphs out of it, there's too much zeros. I don't want to divide all the numbers per 1 million. Is there a way to custom format those numbers with a mask that would take care of that problem for me ? i.e. 7 456 890.98$ would be represented with 7,457 M$ tia Double-click the numbers on your chart. Hit the Scale tab. From the Display units drop-down, choose Millions. ******************* ~Anne Troy www.OfficeArticles.com "Junkyard Engineer" <jevandenbroucke@hotmail.com> wrote in messag...

Changing cell format
I have a worksheet that is populated with data exported from Access. The data in the Access table was serial numbers with an occasional (rare) alpha character. The column was formatted "Text" in Access. When I exported it to Excel, every value in this field (around 3000 entries), has an apostrophe (') inserted at the frontof the cell. Find and Replace does not see the ' How can I remove this so that I can use Fill>Series to populate the gaps in my data (there are many gaps)? Try copying the ' Access character and pasting it into the Replace menu. It could be...

Expand capabilities of Time Off requests in Business Portal.
The current capabilities of managing time-off requests in Great Plains is very limited. I would like the ability to set-up more than Vacation/Sick time-off codes, and this should flow through to Business Portal so employees can specify if the time-off is vacation, sick, personal, floating holiday, etc... ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the ...