473,324 Members | 2,473 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Automatic Time Formatting

Hello:

Just wondering how to go about having my cells automatically change 0800 into 08:00 so that I don't have input the : everytime.

Thanks for the help!
Oct 2 '07 #1
12 1305
Frinavale
9,735 Expert Mod 8TB
Hello:

Just wondering how to go about having my cells automatically change 0800 into 08:00 so that I don't have input the : everytime.

Thanks for the help!
What language are you implementing in?
What types of objects are your "cells"?
Oct 2 '07 #2
coolgames
169 100+
please be specific..
i don't know what type of cells you are talking about...

Excel ??
Oct 3 '07 #3
Excel cells

Sorry
Oct 3 '07 #4
Scott Price
1,384 Expert 1GB
In Excel 2003:

Right click the cell or range of cells, choose Format Cells. On the first tab in the dialog box that appears, choose Custom (bottom of list). Click on one of the formats, and then enter this: 00":"00 in the Type text box.

Regards,
Scott
Oct 3 '07 #5
FishVal
2,653 Expert 2GB
Nice, Scott. Much more better than the event handler I've almost posted. LOL.
Oct 3 '07 #6
Okay that was simple and does the trick.

I coudl fall in love with ya'll over all this hehe
Oct 3 '07 #7
Scott Price
1,384 Expert 1GB
Glad it works for you!

The simpler the better IMHO :-)

Regards,
Scott
Oct 3 '07 #8
Scott Price
1,384 Expert 1GB
Nice, Scott. Much more better than the event handler I've almost posted. LOL.
Thanks, Fish!

Would be interesting to see the event handler, though... It could probably have a few added features that make it more powerful?

Regards,
Scott
Oct 3 '07 #9
FishVal
2,653 Expert 2GB
Something like this.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.  
  3.     Static blnIsExecuted As Boolean
  4.  
  5.     If blnIsExecuted Then Exit Sub
  6.  
  7.     With Target
  8.         'Somewhat speculative cell format check, should be smthng else
  9.         If InStr(1, .NumberFormat, "h:mm") > 2 Or _
  10.             InStr(1, .NumberFormat, "h:mm") < 1 Then Exit Sub
  11.         blnIsExecuted = True
  12.         .Value = Int(.Value / 100) & ":" & Format(.Value Mod 100, "00")
  13.         blnIsExecuted = False
  14.     End With
  15.  
  16. End Sub
  17.  
Oct 3 '07 #10
Frinavale
9,735 Expert Mod 8TB
Something like this.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.  
  3.     Static blnIsExecuted As Boolean
  4.  
  5.     If blnIsExecuted Then Exit Sub
  6.  
  7.     With Target
  8.         'Somewhat speculative cell format check, should be smthng else
  9.         If InStr(1, .NumberFormat, "h:mm") > 2 Or _
  10.             InStr(1, .NumberFormat, "h:mm") < 1 Then Exit Sub
  11.         blnIsExecuted = True
  12.         .Value = Int(.Value / 100) & ":" & Format(.Value Mod 100, "00")
  13.         blnIsExecuted = False
  14.     End With
  15.  
  16. End Sub
  17.  
I didn't even know Excel had event handlers :P
I'm going to have to take a look at it more!
Oct 3 '07 #11
NeoPa
32,556 Expert Mod 16PB
I typically use a global flag to indicate that the code is already running, as other routines also need to know, but the static internal one also works well in this specific case.
Expand|Select|Wrap|Line Numbers
  1. 'This routine ensures the figure is only updated when appropriate
  2. 'It relies on a variable (blnBlock) defined (as Public) in a standard code 
  3. 'module within the project.  You can add other criteria if required.
  4. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  5.   With Target
  6.     If blnBlock _
  7.     Or InStr(1, .Address, ":") > 0 _
  8.     Or InStr(1, .Address, ",") > 0 _
  9.     Or InStr(1, .Value, ":") > 0 Then Exit Sub
  10.     blnBlock = True
  11.     .Value = Format(.Value, "00:00")
  12.     'If a text result is explicitly required use the following line instead
  13.     '.Value = Format(.Value, "'00:00")
  14.     blnBlock = False
  15.   End With
  16. End Sub
Oct 3 '07 #12
NeoPa
32,556 Expert Mod 16PB
I didn't even know Excel had event handlers :P
I'm going to have to take a look at it more!
If you look in the VBA window (Alt-F11) you will see a couple of drop-downs in the Code Pane (F7). The one on the left allows you to select an object and then the one on the right lists the possible events to handle. If you select one it will even create a skeleton routine for you (empty of course).
Oct 3 '07 #13

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: svilen | last post by:
hello again. i'm now into using python instead of another language(s) for describing structures of data, including names, structure, type-checks, conversions, value-validations, metadata etc....
19
by: Mel | last post by:
when downloading files from my site, when file types are known (i.e *.doc) browsers open the file for viewing. is there a way to disable that and just present the save as dialog (same as for...
1
by: Dymov Vlad | last post by:
Hello, All! I have some trouble with subject. Maybe it is not trouble at all, but i need a good advise. I have just tried to write simple ASPX page in HTML view. Everything was good....
6
by: Matt | last post by:
I have begun designing and programming in C++ after a several years away from software engineering/programming. I am looking for an "automatic C++ header generator." I have what seems to be...
4
by: pjac | last post by:
I need assistance in creating an Automatic tracking number that appears in a textbox that looks like: 2004-001. The first part of the number is based on the year, the second part is generated from...
4
by: m_houllier | last post by:
STUDENT TABLE StudentReference Student Name etc ATTENDANCE TABLE AttendanceID CourseID StudentReference
1
by: Michel Esber | last post by:
Hello, Linux RedHat AS4 running DB2 V8 FP11. I have followed the docs at http://tinyurl.com/qckrn and enabled automatic statistics collection. It has been 2 days since I updated my DB cfg and...
1
by: Russell Mangel | last post by:
I am using VS2005. When I paste source code into the code window, VS2005 formats like the following: public MsgRecipientReader() : base() { }
58
by: Jorge Peixoto de Morais Neto | last post by:
I was reading the code of FFmpeg and it seems that they use malloc just too much. The problems and dangers of malloc are widely known. Malloc also has some overhead (although I don't know what is...
2
by: bryars | last post by:
I want to write some SQL which results in an automatic conversion of a datetime to a string in a format suitable for the Language of the connection (either by explicitly setting the Language in the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.