2 Questions

[paramedic_dom]

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

 

[cht13er]

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

 

[paramedic_dom]

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]

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

Not sure why .. are you placing the code in the sheet's code section
(not in a module)? It should be under "Sheet1" or whatever...

C