I wanta be lazy with 01:30 &17:30 input time (Att: Steve) Wo

[cblincoln43]

I saw and can't find how to enter time as 1730 & 0100, and have it end up in the cell as 5:30pm or 1:00am. But not change the calculation for start and end time to hours.

I search i learn that i should have bookmarked 6,000 + items i love every time i go there. Thanks in advance for the help. Back to Search to Learn.
This message was edited by cblincoln43 on 2002-09-01 18:18
This message was edited by cblincoln43 on 2002-09-04 12:16

 

[PaddyD]

from Dave McRitchie @

http://www.mvps.org/dmcritchie/excel/datetime.htm

(he sites Glenn Schwandt for te forumla itself):

=ROUNDDOWN(A2,-2) / 2400 + MOD(A2,100) / 1440


Paddy

 

[bullit_nl]

Please let me know if u figured it out how to do it.

Thanks

 

[cblincoln43]

Have given it more thought! I don't think it was a formula that did this conversion. When you ENTER the time into the cell and delete it you lose both time & formula. may have been vba or format (?) I just know the little lady that posted it was given a slick way to do it. She ENTERED time wrong and it corected it to the proper time format.
Still searching.

 

[stevebausch]

Well, there are possibly two ways to enter time without a delimiter, and have it automatically formatted.

Not that I know them off the top of my head.

One would involve a custom formatting.

The other would involve WorkSheet_Change, and that's where this formula MIGHT be useful.

Workbook_Change has a Target as Range argument; your code would perform an Intersect of Target against the cells where you desire this behavior.

If you are in the correct cell(s), then Target= the formula.
This message was edited by stevebausch on 2002-09-02 08:15

 

[stevebausch]

OOPs! The Typo Monster has struck! Formula works as advertised. Sorry about that.

Since I am also interested in this, watch this thread, see what develops.
This message was edited by stevebausch on 2002-09-02 08:00

 

[Dave Patton]

On 2002-09-02 07:32, cblincoln43 wrote:
Have given it more thought! I don't think it was a formula that did this conversion. When you ENTER the time into the cell and delete it you lose both time & formula. may have been vba or format (?) I just know the little lady that posted it was given a slick way to do it. She ENTERED time wrong and it corected it to the proper time format.
Still searching.

Right click on the sheet name tab and select "view Code".

If there are just 2 lines there, delete the default information and paste the following.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then
If IsNumeric(Target) Then
Application.EnableEvents = False
Target = Left(Target, 2) & ":" & Right(Target, 2)
Application.EnableEvents = True
End If
End If
End Sub

 

[stevebausch]

Dave is our hero today. Thanks Dave!

The above formula needs some parentheses to keep it honest.

=(ROUNDDOWN(RC[-1],-2)/2400)+(MOD(RC[-1],100)/1440)

 

[stevebausch]

For the Truly Lazy:



Private Sub Worksheet_Change(ByVal Target As Range)
Dim TempStr As String
If Not Intersect(Target, Range("A1:L40")) Is Nothing Then
If IsNumeric(Target) Then
Application.EnableEvents = False
TempStr = Target
If Len(TempStr)< 4 Then 'we need to pad the entry with leading "0"s
Beep

TempStr = String(4 - Len(TempStr), "0") & TempStr
Debug.Print TempStr
Else
Debug.Print TempStr
End If

Target = Left(TempStr, 2) & ":" & Right(TempStr, 2)
Application.EnableEvents = True
End If
End If
End Sub
This message was edited by stevebausch on 2002-09-02 09:45

 

[cblincoln43]

??????? Im confused! I can now enter 17:00 and the format changes it to 5:00pm and still calculates. In these codes i don't see the am, pm format. Also I just read chip pearson, date and time sheet and im going to see what i can mess up playing with it. Bob, Thanks PaddyD. Im still Searching.