2 Questions
 

These will probably show my ignorance but, 2 ques.

I've been asked to look at a spreadsheet and look at inhancing it. The
ques. I have to ask are these:

I want a formula to look at the prev. cell and if ther is a number in the
cell than to add 3 hrs (h:m) to an entered time and place that number in a
cell and add the number of hrs in decimal form in another cell.

Second thing is to not allow next entry (hr:m) if it is within the 3 hrs if
the above exists.

Does that make sense and/or can it be done?

Many thanks in advance,

Dom

2 Questions
 

On May 2, 11:01*am, paramedic_dom
wrote:
These will probably show my ignorance but, 2 ques.

I've been asked to look at a spreadsheet and look at inhancing it. *The
ques. I have to ask are these:

I want a formula to look at the prev. cell and if ther is a number in the
cell than to add 3 hrs (h:m) to an entered time and place that number in a
cell and add the number of hrs in decimal form in another cell.

Second thing is to not allow next entry (hr:m) if it is within the 3 hrs if
the above exists.

Does that make sense and/or can it be done?

Many thanks in advance,

Dom

To answer your third question first: yes, it can be done.

Let's say that the entered time is in A1. The formula to find the time
3 hours from A1 will then be in B1 and will read
=IF(ISNUMBER(A1),A1+TIME(3,,),"")
The formula in C1 will convert the time in B1 to hours:
=IF(ISNUMBER(B1),HOUR(B1)+MINUTE(B1)/60+SECOND(B1)/3600,"")

To restrict the user from entering a time within three hours of the
time above it, paste this into the appropiate sheet's code (Hit Alt
+F11 to get into the Visual Basic Editor and then doubleclick on the
sheet's name on the left panel called the Project Explorer:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Len(Target) 0 Then
If IsNumeric(Range(Target.Address).Offset(-1, 0).Value) = True And
IsNumeric(Range(Target.Address).Value) = True Then
If Target.Value - (3 / 24) < Range(Target.Address).Offset(-1,
0).Value Then
Call MsgBox("Can't enter in a time less than three hours
after the cell above.")
Range(Target.Address).ClearContents
End If
End If
End If

End Sub



HTH

Chris

2 Questions
 

Thanks Chris,

You're twigging my college excel programming days - vaguely, now I'm getting
a syntax error @ the

Private Sub_worksheet - line

I know I'm probably scfewing something up (this is why I got out of
programming)

Thanks Dom

"cht13er" wrote:

On May 2, 11:01 am, paramedic_dom
wrote:
These will probably show my ignorance but, 2 ques.

I've been asked to look at a spreadsheet and look at inhancing it. The
ques. I have to ask are these:

I want a formula to look at the prev. cell and if ther is a number in the
cell than to add 3 hrs (h:m) to an entered time and place that number in a
cell and add the number of hrs in decimal form in another cell.

Second thing is to not allow next entry (hr:m) if it is within the 3 hrs if
the above exists.

Does that make sense and/or can it be done?

Many thanks in advance,

Dom

To answer your third question first: yes, it can be done.

Let's say that the entered time is in A1. The formula to find the time
3 hours from A1 will then be in B1 and will read
=IF(ISNUMBER(A1),A1+TIME(3,,),"")
The formula in C1 will convert the time in B1 to hours:
=IF(ISNUMBER(B1),HOUR(B1)+MINUTE(B1)/60+SECOND(B1)/3600,"")

To restrict the user from entering a time within three hours of the
time above it, paste this into the appropiate sheet's code (Hit Alt
+F11 to get into the Visual Basic Editor and then doubleclick on the
sheet's name on the left panel called the Project Explorer:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Len(Target) 0 Then
If IsNumeric(Range(Target.Address).Offset(-1, 0).Value) = True And
IsNumeric(Range(Target.Address).Value) = True Then
If Target.Value - (3 / 24) < Range(Target.Address).Offset(-1,
0).Value Then
Call MsgBox("Can't enter in a time less than three hours
after the cell above.")
Range(Target.Address).ClearContents
End If
End If
End If

End Sub



HTH

Chris