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

 

[stevebausch]

If the format is bothering you, that is a matter of Format-Cells.

You want military time, right? I recall that's the second formatting option offered, under Time

 

[cblincoln43]

Ok, Steve. now im going to put my 5lber (hammer) next to the garage door so when my confusion becomes total I can smach this computer into small working pieces. Wanted 1:00am & 5:00pm and it's there to.
This message was edited by cblincoln43 on 2002-09-02 09:08

 

[stevebausch]

I suspect some of your confusion is due to my code....sorry about that.

Let's try this:

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

Change the Range area as you see fit.
Format the cells to Time, with your beloved AM/PM format.

This code will accept any length of entry; one digit, two digits, three digits, or four.

It does expect you to add 12 hours to the time, IF you expect it to return a PM time.

 

[cblincoln43]

Steve
I don't how to list other threads, so . There is more on this subject posted by bullit_nl, on 8/21/02 @ o2:30. Also add 12 hours ?. Can I asume you mean in the formulas calculation?
This message was edited by cblincoln43 on 2002-09-02 17:06

 

[stevebausch]

When you key it in, if you don't add 12 hours (to indicate) PM, the formula is clueless; since it can't read your mind.

If you know a way that a formula can take numbers only, without adding 12 (in the case of PM), and return the proper AM/PM notation, let me know..........

I assume that code could look for a + sign, or something, to notate AM or PM, but it's pretty simple to add 12 hours (in your head) and key it in.

BTW, what level of user are you attempting to satisfy?

There's a guy at work, who asks for help getting into his email. It WOULD help if he remembered his password, and it WOULD be useful if he remembered his logon name.

 

[cblincoln43]

The 5 lb-er i mention earler is now at the side of my computer. Don't think i will sue it on the computer though. Last night at work my mind slowed down enough that "POP" I recalled something about if one adds 12 to time after noon one gets 17:00 or 5:00pm. Think I will let the little girl i married a few days ago (14,765, on 9/9/02) have th hammer to strighten me out instead.
At any rate I am very greatfull for you interest and of course ALL of your help. The level heck I am just an old man playing with a costom time sheet that is now in use at a company with 1500 people. THANKS to ALL you great people on this board, that share so much for so little to people like me. Ive only had this thing about a year and a half and ive learned so much about this right here that my company think im a wizeeeeeeeeee.
Very greatfull Bob, cblincoln43

 

[cblincoln43]

The 5 lb-er i mention earler is now at the side of my computer. Don't think i will sue it on the computer though. Last night at work my mind slowed down enough that "POP" I recalled something about if one adds 12 to time after noon one gets 17:00 or 5:00pm. Think I will let the little girl i married a few days ago (14,765, on 9/9/02) have th hammer to strighten me out instead.
At any rate I am very greatfull for you interest and of course ALL of your help. The level heck I am just an old man playing with a costom time sheet that is now in use at a company with 1500 people. THANKS to ALL you great people on this board, that share so much for so little to people like me. Ive only had this thing about a year and a half and ive learned so much about this right here that my company think im a wizeeeeeeeeee.
Very greatfull Bob, cblincoln43

 

[cblincoln43]

This message was edited by cblincoln43 on 2002-09-04 12:36

 

[cblincoln43]

Steve, THANKS your code works Great.
Bob,cblincoln