converting a 4 figure number to time in active cell

Hi, I've got 2007 on my pc and I'm trying to set up a timesheet. As I'm to 
lazy to keep entering the ":" all the time, i was wondering if someone knew 
how to change 1530 to 15:30 etc in the active cell for all cells G5 to I19 
on all 5 sheets in my workbook!!!

I think this has something to do with vba or macros and i've never used them 
before.

If anyone can help me, I'd be very grateful.

Thanks
Pete 

0
my.usenet (3)
4/2/2009 8:41:14 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
386 Views

Similar Articles

[PageSpeed] 48

Chip Pearson shows how you can do that here:

'Date And Time Entry'
(http://www.cpearson.com/excel/DateTimeEntry.htm)

Hope this helps.

Pete

Pee;294417 Wrote: 
> Hi, I've got 2007 on my pc and I'm trying to set up a timesheet. As I'm
> to
> lazy to keep entering the ":" all the time, i was wondering if someone
> knew
> how to change 1530 to 15:30 etc in the active cell for all cells G5 to
> I19
> on all 5 sheets in my workbook!!!
> 
> I think this has something to do with vba or macros and i've never used
> them
> before.
> 
> If anyone can help me, I'd be very grateful.
> 
> Thanks
> Pete


-- 
Pete_UK
------------------------------------------------------------------------
Pete_UK's Profile: http://www.thecodecage.com/forumz/member.php?userid=205
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=82294

0
4/2/2009 11:45:15 PM
Thanks for this, I've got it sort of working but only for the g column.
Any suggestions on how to get it working for g5-g19 and h5-19 and I5-i19?

Thanks again,
Pete



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Range("G5:G19")) Is Nothing Then
    Exit Sub

End If
If Target.Cells.Count > 1 Then
    Exit Sub
End If
If Target.Value = "" Then
    Exit Sub
End If

Application.EnableEvents = False
With Target
If .HasFormula = False Then
    Select Case Len(.Value)
        Case 1 ' e.g., 1 = 00:01 AM
            TimeStr = "00:0" & .Value
        Case 2 ' e.g., 12 = 00:12 AM
            TimeStr = "00:" & .Value
        Case 3 ' e.g., 735 = 7:35 AM
            TimeStr = Left(.Value, 1) & ":" & _
            Right(.Value, 2)
        Case 4 ' e.g., 1234 = 12:34
            TimeStr = Left(.Value, 2) & ":" & _
            Right(.Value, 2)
        Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
            TimeStr = Left(.Value, 1) & ":" & _
            Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
        Case 6 ' e.g., 123456 = 12:34:56
            TimeStr = Left(.Value, 2) & ":" & _
            Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
        Case Else
            Err.Raise 0
    End Select
    .Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub





"Pete_UK" <Pete_UK.3q1j10@thecodecage.com> wrote in message 
news:Pete_UK.3q1j10@thecodecage.com...
>
> Chip Pearson shows how you can do that here:
>
> 'Date And Time Entry'
> (http://www.cpearson.com/excel/DateTimeEntry.htm)
>
> Hope this helps.
>
> Pete
>
> Pee;294417 Wrote:
>> Hi, I've got 2007 on my pc and I'm trying to set up a timesheet. As I'm
>> to
>> lazy to keep entering the ":" all the time, i was wondering if someone
>> knew
>> how to change 1530 to 15:30 etc in the active cell for all cells G5 to
>> I19
>> on all 5 sheets in my workbook!!!
>>
>> I think this has something to do with vba or macros and i've never used
>> them
>> before.
>>
>> If anyone can help me, I'd be very grateful.
>>
>> Thanks
>> Pete
>
>
> -- 
> Pete_UK
> ------------------------------------------------------------------------
> Pete_UK's Profile: http://www.thecodecage.com/forumz/member.php?userid=205
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=82294
> 

0
my.usenet (3)
4/3/2009 6:45:25 AM
Doh, the answer was blindingly obvious!

If Application.Intersect(Target, Range("G5:I19")) Is Nothing Then

Cool - thanks again, I'm most grateful to you

:o)



"Pee" <my.usenet@blueyondr.co.uk> wrote in message 
news:BgiBl.263146$Bt3.58256@newsfe03.ams2...
> Thanks for this, I've got it sort of working but only for the g column.
> Any suggestions on how to get it working for g5-g19 and h5-19 and I5-i19?
>
> Thanks again,
> Pete
>
>
>
> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As 
> Range)
> Dim TimeStr As String
>
> On Error GoTo EndMacro
> If Application.Intersect(Target, Range("G5:G19")) Is Nothing Then
>    Exit Sub
>
> End If
> If Target.Cells.Count > 1 Then
>    Exit Sub
> End If
> If Target.Value = "" Then
>    Exit Sub
> End If
>
> Application.EnableEvents = False
> With Target
> If .HasFormula = False Then
>    Select Case Len(.Value)
>        Case 1 ' e.g., 1 = 00:01 AM
>            TimeStr = "00:0" & .Value
>        Case 2 ' e.g., 12 = 00:12 AM
>            TimeStr = "00:" & .Value
>        Case 3 ' e.g., 735 = 7:35 AM
>            TimeStr = Left(.Value, 1) & ":" & _
>            Right(.Value, 2)
>        Case 4 ' e.g., 1234 = 12:34
>            TimeStr = Left(.Value, 2) & ":" & _
>            Right(.Value, 2)
>        Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
>            TimeStr = Left(.Value, 1) & ":" & _
>            Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
>        Case 6 ' e.g., 123456 = 12:34:56
>            TimeStr = Left(.Value, 2) & ":" & _
>            Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
>        Case Else
>            Err.Raise 0
>    End Select
>    .Value = TimeValue(TimeStr)
> End If
> End With
> Application.EnableEvents = True
> Exit Sub
> EndMacro:
> MsgBox "You did not enter a valid time"
> Application.EnableEvents = True
> End Sub
>
>
>
>
>
> "Pete_UK" <Pete_UK.3q1j10@thecodecage.com> wrote in message 
> news:Pete_UK.3q1j10@thecodecage.com...
>>
>> Chip Pearson shows how you can do that here:
>>
>> 'Date And Time Entry'
>> (http://www.cpearson.com/excel/DateTimeEntry.htm)
>>
>> Hope this helps.
>>
>> Pete
>>
>> Pee;294417 Wrote:
>>> Hi, I've got 2007 on my pc and I'm trying to set up a timesheet. As I'm
>>> to
>>> lazy to keep entering the ":" all the time, i was wondering if someone
>>> knew
>>> how to change 1530 to 15:30 etc in the active cell for all cells G5 to
>>> I19
>>> on all 5 sheets in my workbook!!!
>>>
>>> I think this has something to do with vba or macros and i've never used
>>> them
>>> before.
>>>
>>> If anyone can help me, I'd be very grateful.
>>>
>>> Thanks
>>> Pete
>>
>>
>> -- 
>> Pete_UK
>> ------------------------------------------------------------------------
>> Pete_UK's Profile: 
>> http://www.thecodecage.com/forumz/member.php?userid=205
>> View this thread: 
>> http://www.thecodecage.com/forumz/showthread.php?t=82294
>>
> 

0
my.usenet (3)
4/3/2009 6:52:04 AM
Reply:

Similar Artilces:

Subtraction of 2 numbers/cells
Could anyone please help me with this situation (in Excel 2003): I have 2 cells A1 = 0 and B1 = 10 In cell C1 i put this formula C1=A1-B1 . Now the answer will be C1= -10 but my question is: Is there a formula to calculate in C1 the result if the answer is positive and if the answer is negative to put 0? Thank you in advance. Try one of two: =(A1-B1)*(A1>B1) =(A1-B1)*(A1>=B1) Micky "Cipri" wrote: > Could anyone please help me with this situation (in Excel 2003): > I have 2 cells > A1 = 0 and B...

Change the formatting of row by select a single cell & Editing should be working
Hi All; I require such a sheet code which can help me in Changing the formatting of row (like A1:A25) of selected cell (A4 is Selected) and allow me to use editing (Cut, Copy, Paste, Undo etc). Currently I'm Using following code but this does not allow me to use editing (Cut, Copy, Paste, Undo etc) and I can not decide the range for working this sheet code. Any kind help is appreciated. Private Sub Search_Click() Sheets("Quick Search").Visible = True ActiveWindow.SelectedSheets.Visible = False Sheets("Quick Search").Select End Sub Private Sub Worksheet_...

How to pick every third cell?
Hi, I have a spreadsheet with a column in which I keep dates. The date appers in every third cell of this column and the other two cells are blank. I have the another sheet in this workbook in which I want those dates to appear in the column in consecutive order. I'm using cell reference comand right now but have to do it manually every time I have a new entries. So in the other sheet there is a column with cells having references to every third cell in the first sheet. I trided to high-lite the multiple bunches of cells and drag-copy it down - but it's not working. Any ideas??? Tha...

How do I get a query to add an incrementally increasing number?
I have a query that selects a subset of records. I need to add a "run number" to that subset, starting with 100. That is, the first record is 100, the second 101, etc. I know there has to be a way to do this, but I can't figure it out You might see if you can get this code to work for you: http://support.microsoft.com/?id=199679 I have not tried it out... Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ __________________________________________ "RNDL" wrote: > I have a query that selects a subset of records....

Apointment time Changes
I have a group of users that when they send apointments or receive apointments they time changes by an hour.. for example they send me a 3:00PM apointment ill get it for a 4:pm apointment.. they are on the same network as i am.. same exchange server... no one else has this problem.. has anyone come across this problem?.. any help would be great! Thanks Chris The settings to adjust for daylight savings time are in Windows as well in Outlook Calendar options. Both need to be checked. -- Bharat Suneja MCSE, MCT -------------------------------- "Chris" <Chris@discussions.m...

Fyi..Support request regarding Windows Live Essential Wave 4 beta
This is a multi-part message in MIME format. ------=_NextPart_000_0178_01CB1938.618266B0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable From the Windows Live Solution Center = http://windowslivehelp.com/thread.aspx?threadid=3D8a67c6f7-8a04-48d0-972e= -1252034d6390 <qp> To differentiate Windows Live Essentials Installer BETA related issues = from the current version, please add the word =93BETA=94 in the = =93Detailed Descriptions=94 to get your questions answered faster when = creating a Post. This will help ou...

Summing Time #2
I wish to calculate the time the machine has actually run. D1 = 22:00 (Start Time) F1= Time Run E1 = 00:15 (Finish Time) D2 = 00:15 (Start) F2= Time Run E2 = 02:30 (Finish) D9 = 22:20 (Breakdown) E9 = 22:30 (Restart) D10 = 23:15 (Breakdown) E10 = 23:30 (Restart) D11 = 00:30 (Breakdown) E11 = 02:00 (Restart) Is there a formula that can scan a range (D9:E11) and give me tha answer in F1 of the time run between the Start & Finish times any help would be appreciated Pete Try: =SUM(E1:E2-D1:D2,-(E9:E11-D9:D11),--(E1:E2<D1:D2),-- (E9:E11<D9:D11)) Array-entered (press ctr...

Some Error that I can't figure out.
Ok I am typing in a bunch of UPC numbers for my data sheet, It will be a couple hundred numbers. The UPC numbers are as follows 012569528628 027616149237 025192051029 012235111536 012569528734 012569508422 12569508439 86162180934 043396039490 043396603820 012569509023 012569509030 12569512429 12569512436 For some odd reason all the numbers without a zero are wrong. All of these numbers need 1 number 0 in front of them. As for the other numbers I get a little green arrow in the upper left corner of the cell. It tells me Something about "Number stored as text" (This is the one...

ask for overtime with time restriction
Hi I would like to know how I could set up an overtime list to where the cell would turn red if you don't ask by a certain time. Example for saturday overtime you must be asked by end of shift thursday so if you are asked on friday it would go red and the same for sunday where you have to be asked by friday. Thanks Karl Lungmus ...

VBA Cell.find
Hello, I need to design a VBA macro that would find "paid" in Column A and would select in value in column H. Ex: Column A Column H Paid $100.00 I need to select the $100.00 Regards Hi why not use application.vlookup? -- Regards Frank Kabel Frankfurt, Germany Jeff wrote: > Hello, > > I need to design a VBA macro that would find "paid" in Column A and > would select in value in column H. > Ex: > Column A Column H > Paid $100.00 > I need to select the $100.00 > Regards Hi, Technically, I'm going ...

Sheet Name Refering to Cell Value
I want to name a sheet to the cell value in A2. Cell A2 has an Alt-enter in the cell. I want the sheet name to be the cell value of the first line only. Can anybody help. TIA Greg One way: Option Explicit Sub testme01() Dim myStr As String Dim vbLFPos As Long With ActiveSheet myStr = .Range("a2").Value vbLFPos = InStr(1, myStr, vblf) If vbLFPos > 0 Then myStr = Left(myStr, vbLFPos - 1) On Error Resume Next .Name = myStr If Err.Number <> 0 Then MsgBox "couldn...

Converting Quatro Pro 5.0 file to Excell
Can this be done. If so, how. I would really like to preserve and use my quatro pro files in excell. Thanks. try the excel newsgroup... >-----Original Message----- >Can this be done. If so, how. I would really like to >preserve and use my quatro pro files in excell. Thanks. >. > ...

how to add only positive number
hi i have a column which has positive as well as negative number but would like to add only positive number, how do i, please let me kno -- Message posted from http://www.ExcelForum.com Hi try =SUMIF(A1:A100,">0") -- Regards Frank Kabel Frankfurt, Germany > hi > i have a column which has positive as well as negative number but i > would like to add only positive number, how do i, > please let me know > > > --- > Message posted from http://www.ExcelForum.com/ ...

Determine if a linked cell contains data
Using vba I'd like to change the formatting/colours on a particular cell if the cell contains text. So for example: If Application.WorksheetFunction.IsText(Cells(i, ColNum)) Then Cells(i, ColNum).Font.Bold = True Cells(i, ColNum).Interior.ColorIndex = 40 End If This works fine for cells which do contain text but not for cells which contain 'linked text'. I have some cells which are linked from a 2nd sheet and in the 2nd sheet they contain text. The code above does not work because there the cell contains ='Sheet2'!B93 How can I get ...

Daylight Savings Time
Since the daylight saving time change. We are having trouble with users calender appointments being 1 hour off. We are using Exchange 5.5/Outlook 2000. In Outlook we had them check the box indicating adjustment for daylight saving time. After 2 days the time changed back an hour again. This seems to be only happening with our XP users only. Any ideas what is causing this. Thanks in advance. My guess is the time zone is wrong on their computer. Make sure the checkbox for "automatically adjust the clock" is set. I usually have users that like to manually change their clocks. ;...

Address of Selection Prior to Clicking on a Cell
If I have cell b5 selected, and I click on cell e5, is there a way for VBA to identify that it came from cell b5 before the click? Code such as this can be set to be triggered when you click on a cell, but can it tell you what cell it came from? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("B5")) Is Nothing Then _ Range("B5").Value = 10 End Sub Thank you, Chris Try the below.. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static pRange As Range If Not pRange Is Nothing Then MsgBo...

can't sum figures in a report ...help please
I have a text box that uses an if statement to determine whether someone is absent in my class or not. I name the text box inattendance. I have a text box called points that assigns points to absent students depending on which class they are currently enrolled in. I want to sum the total points for each student. I do a simple =sum([points]) and when I run the report I get a box that just says points indicating that it doesn't have a definition for points. I used the name property of the text box and called it points. any suggestions on alernate methods or on what I am do...

Run-time error 3077
Hello Everybody, In Access 2003, I am using VBA to find a string in a table. Every time I try to execute following line: anRST.FindFirst "John Smith" I get "Run-time error '3077':" which says: Syntax error (missing operator) in expression. Same is true for FindNext, FindLast and FindPrevious. anRST was defined using following code: Set anRST = CurrentDb.OpenRecordset("Test", dbOpenDynaset) Where "Test" is the name of the table. Any ideas what I am missing? Thank you, -- Syed Zeeshan Haider -----------------------------------------...

Want Table cells in Word to format like in Excel
Version: 2008 Operating System: Older version (Leopard 9.1) <br> I am trying to do up an invoice in Word and am using tables within it. I want to be able to utilise the Cells for various formats/categories, such as, time, numbers with varying decimal places and accounting ($0.00), like in Excel. Is there a way to do this as I am currently stumped? <br> I don't really want to use Excel for invoicing as most of our invoices are emailed and I don't want the whole 'worksheet' viewable. Unless of course there is a way to make an Excel Spreadsheet to ONLY be one pag...

can a column for 2 different words and display a number based on how many are found
This should be a very easy thing to do, but I can�t figure it out. Please Help. Thanks! I am trying to scan a column for 2 different words and display a numbe based on how many are found. Example: If the words �Completed� and �N/A� are found within a column as liste below. I want to create a formula that will display the number 5 because those words are found 5 times within that column. Completed Completed Completed N/A Complete -- Message posted from http://www.ExcelForum.com One way: =COUNTIF(A:A,"Completed")+COUNTIF(A:A,"N/A") for column A Regards Trevor ...

Run-time error '424'
Can anyone advise why I am getting a run-time error with this line (and what I need to do to get it working?): Me.Parent!Form.Bookmark = rst.Bookmark 'reposition the form Here is the code I am trying to run: Sub Form_Click() Dim rs As DAO.Recordset If Me.Parent.Dirty Then Me.Parent.Dirty = False End If 'Search in the clone set. Set rs = Me.RecordsetClone rs.FindFirst "[RecID] = '" & Me.RecID & "'" If Not rs.NoMatch Then Me.P...

Colour cell to return date
Hi all, this is what i wish to have happen; i have created a list of activities B5:B25 say, next to them i have two column Start & Finish D & E, next to them i have columns G to J, where G1:J1 has date fields in cells 8th Jan 10, 15th Jan 10, to 29th Jan 10 week between them. The question is how can i retrurn a date in cell D5 if i populate G5 (or fill with a colour)? Hi, see Chip explanation Color Change And Calculation Excel normally calculates the formula in a cell when a cell upon which that formula depends changes. For example, the formula =SUM(A1:A10) is reca...

convert Publisher version 2 files to Publisher 2003?
Does Microsoft have a converter for converting Publisher version 2 files to Publisher 2003 or even to Publisher 2000? Publisher 2003 should open your 2.0 files. Are you getting an error? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "JAB" <JAB@discussions.microsoft.com> wrote in message news:C87D6C52-7810-48B1-9DD5-E221827B19B1@microsoft.com... > Does Microsoft have a converter for converting Publisher version 2 files to > Publisher 2003 or even to Publisher 2000? Thanks for your response Mary. Yes I get ...

Displaying large numbers in a cell
In a worksheet that one of my co-workers uses, she needs to input numbers that are up to 16 characters long. However, Excel for some reason is changing the last number entered to a 0. For example, if she entered 4223980067104216, Excel would change it to 4223980067104210. If she entered 4223980067104212, Exel will change it to 4223980067104210. I have tried changing the column widths, cell formatting, check the options under Tools, but I am not able to find anything to keep this from occuring. I have noticed that Excel does not make this change if only 15 numbers are entered into a ...

Converting from Quicken Deluxe 2004 to MS Money 2004 Deluxe
I downloaded and installed MS Money trial version and after trying to bring across my Quicken QDF file, Money has the accounts but no entries in the check registers. I tried both the File/ Import and the File/ Convert function. I do not use passwords. How do I get the rest of my Quicken data across? I am using Win 2000 pro. Asked and answered several times a week. Mx can't import Qx and is not documented as being able to, Mx can only import Qx-1 or less. Mx and Qx come out at the same time. Money designers are not clairvoyant about what the Quicken designers are doing. "Al(NC)&qu...