I have a log sheet where a nurse will enter the time in and time out. I have
a formula giving the total time in clinic. What is the best way to set up
formatting for entry?

I don't want the nurse to be required to type a colon, but I do need her to
do AM or PM.

My formula for figuring the time duration is:

=TEXT(IF(M2-L20,M2-L2,""),"h:mm")

Is this the best formula for ease of entry?

I would like her to type time in to be "130p" and time out "215p" and my
formula to display "00:45" I need am/pm so that it can figure time in
"1245a" and time out "115p"

I need help!!!

Cyrus

How about just having the Nurse select the cell where the entry is supposed
to go and then hitting a Button to run this macro..........

Sub TimeStamp()
' Select a cell and run the macro to insert
' the current date and time
ActiveCell.FormulaR1C1 = Date & " " & Time
End Sub

It will automatically enter a perfect current Date and Time Stamp

Vaya con Dios,
Chuck, CABGx3


"Cyrus" wrote:

I have a log sheet where a nurse will enter the time in and time out. I have
a formula giving the total time in clinic. What is the best way to set up
formatting for entry?

I don't want the nurse to be required to type a colon, but I do need her to
do AM or PM.

My formula for figuring the time duration is:

=TEXT(IF(M2-L20,M2-L2,""),"h:mm")

Is this the best formula for ease of entry?

I would like her to type time in to be "130p" and time out "215p" and my
formula to display "00:45" I need am/pm so that it can figure time in
"1245a" and time out "115p"

I need help!!!

Cyrus

I've had a shot at amending Chip Pearson's time entry routine for you

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String
Dim fPM As Boolean
On Error GoTo EndMacro
If Application.Intersect(Target, Range("A1:A10")) 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 UCase(Right(.Value, 2)) = "PM" Then
fPM = True
.Value = Left(.Value, Len(.Value) - 2)
ElseIf UCase(Right(.Value, 2)) = "AM" Then
.Value = Left(.Value, Len(.Value) - 2)
End If
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
If fPM Then
TimeStr = Left(TimeStr, InStr(1, TimeStr, ":") - 1) + 12 & _
":" & Right(TimeStr, Len(TimeStr) - InStr(1, TimeStr, ":"))
End If
.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

'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

Bob Phillips

(remove nothere from email address if mailing direct)

"Cyrus" wrote in message
...
I have a log sheet where a nurse will enter the time in and time out. I
have
a formula giving the total time in clinic. What is the best way to set
up
formatting for entry?

I don't want the nurse to be required to type a colon, but I do need her
to
do AM or PM.

My formula for figuring the time duration is:

=TEXT(IF(M2-L20,M2-L2,""),"h:mm")

Is this the best formula for ease of entry?

I would like her to type time in to be "130p" and time out "215p" and my
formula to display "00:45" I need am/pm so that it can figure time in
"1245a" and time out "115p"

I need help!!!

Cyrus

Great idea, but the nurse will be entering this data at the end of the day so
a time stamp won't work.

Cyrus

"CLR" wrote:

How about just having the Nurse select the cell where the entry is supposed
to go and then hitting a Button to run this macro..........

Sub TimeStamp()
' Select a cell and run the macro to insert
' the current date and time
ActiveCell.FormulaR1C1 = Date & " " & Time
End Sub

It will automatically enter a perfect current Date and Time Stamp

Vaya con Dios,
Chuck, CABGx3


"Cyrus" wrote:

I have a log sheet where a nurse will enter the time in and time out. I have
a formula giving the total time in clinic. What is the best way to set up
formatting for entry?

I don't want the nurse to be required to type a colon, but I do need her to
do AM or PM.

My formula for figuring the time duration is:

=TEXT(IF(M2-L20,M2-L2,""),"h:mm")

Is this the best formula for ease of entry?

I would like her to type time in to be "130p" and time out "215p" and my
formula to display "00:45" I need am/pm so that it can figure time in
"1245a" and time out "115p"

I need help!!!

Cyrus

Will this stamp a time? The nurse will not be entering the data at the
actual time - she will be keying at the end of the day, so she needs to
actually key in the data.

"Bob Phillips" wrote:

I've had a shot at amending Chip Pearson's time entry routine for you

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String
Dim fPM As Boolean
On Error GoTo EndMacro
If Application.Intersect(Target, Range("A1:A10")) 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 UCase(Right(.Value, 2)) = "PM" Then
fPM = True
.Value = Left(.Value, Len(.Value) - 2)
ElseIf UCase(Right(.Value, 2)) = "AM" Then
.Value = Left(.Value, Len(.Value) - 2)
End If
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
If fPM Then
TimeStr = Left(TimeStr, InStr(1, TimeStr, ":") - 1) + 12 & _
":" & Right(TimeStr, Len(TimeStr) - InStr(1, TimeStr, ":"))
End If
.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

'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

Bob Phillips

(remove nothere from email address if mailing direct)

"Cyrus" wrote in message
...
I have a log sheet where a nurse will enter the time in and time out. I
have
a formula giving the total time in clinic. What is the best way to set
up
formatting for entry?

I don't want the nurse to be required to type a colon, but I do need her
to
do AM or PM.

My formula for figuring the time duration is:

=TEXT(IF(M2-L20,M2-L2,""),"h:mm")

Is this the best formula for ease of entry?

I would like her to type time in to be "130p" and time out "215p" and my
formula to display "00:45" I need am/pm so that it can figure time in
"1245a" and time out "115p"

I need help!!!

Cyrus

Try it and tell us if it works or how it doesn't.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Cyrus" wrote in message
...
Will this stamp a time? The nurse will not be entering the data at the
actual time - she will be keying at the end of the day, so she needs to
actually key in the data.

"Bob Phillips" wrote:

I've had a shot at amending Chip Pearson's time entry routine for you

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String
Dim fPM As Boolean
On Error GoTo EndMacro
If Application.Intersect(Target, Range("A1:A10")) 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 UCase(Right(.Value, 2)) = "PM" Then
fPM = True
.Value = Left(.Value, Len(.Value) - 2)
ElseIf UCase(Right(.Value, 2)) = "AM" Then
.Value = Left(.Value, Len(.Value) - 2)
End If
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
If fPM Then
TimeStr = Left(TimeStr, InStr(1, TimeStr, ":") - 1) + 12 & _
":" & Right(TimeStr, Len(TimeStr) - InStr(1, TimeStr, ":"))
End If
.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

'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

Bob Phillips

(remove nothere from email address if mailing direct)

"Cyrus" wrote in message
...
I have a log sheet where a nurse will enter the time in and time out.
I
have
a formula giving the total time in clinic. What is the best way to
set
up
formatting for entry?

I don't want the nurse to be required to type a colon, but I do need
her
to
do AM or PM.

My formula for figuring the time duration is:

=TEXT(IF(M2-L20,M2-L2,""),"h:mm")

Is this the best formula for ease of entry?

I would like her to type time in to be "130p" and time out "215p" and
my
formula to display "00:45" I need am/pm so that it can figure time in
"1245a" and time out "115p"

I need help!!!

Cyrus

I do not understand how to do this. I've opened view code and pasted, saved
and go back into worksheet and it's still not displaying.

I want to type 1230p and it display "12:30 PM"

I appreciate your help

Cyrus

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

Bob Phillips

(remove nothere from email address if mailing direct)

"Cyrus" wrote in message
...
I do not understand how to do this. I've opened view code and pasted,
saved
and go back into worksheet and it's still not displaying.

I want to type 1230p and it display "12:30 PM"

I appreciate your help

Cyrus

I know nothing about VB.

I right clicked on tab, view code, pasted your code into my worksheet 1,
saved and closed VB. I then went into worksheet and typed "1230p" and it
still doesn't display "12:30 PM"

Thanks for being so patient with me...just don't know anything about Visual
Basic.

Cyrus,

why don't you mail me the workbook

bob (dot) phillips (at) tiscali (dot) co (dot) uk

do the obvious with the bits in brackets

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Cyrus" wrote in message
...
I know nothing about VB.

I right clicked on tab, view code, pasted your code into my worksheet 1,
saved and closed VB. I then went into worksheet and typed "1230p" and it
still doesn't display "12:30 PM"

Thanks for being so patient with me...just don't know anything about
Visual
Basic.