# cell value between to times!!!

peug417 20:52 31 Jul 06
Locked

working on a spreadsheet to present to a government minister tomorrow to secure more funding for a scheme, I have hit a wall. I have a col (A) which contains times of events have occurred (24 hour clock) in another cell I have a 3 hour time window ie 01:00 - 04:00. c1 has 01:00 and e1 = c1+3. Had to change cell format to numbers with 2 decimal points as +3 wouldn't work on time format. The main problem is I need a formula that calculates whether any of times in col (A) are between the 3 hour window in cells c1 and e1. Any Ideas?

peug417 23:26 31 Jul 06

<bump

johnnyrocker 23:31 31 Jul 06

access might be better?

johnny.

peug417 20:12 04 Aug 06

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Integer
Dim iCnt As Byte
Had the resolution from Whisperer using vb code rather than formulae.

If Target <> [A23] Then Exit Sub
StartVal = [A23].Value
EndVal = [C23].Value
iCnt = 0
iRow = 3
Do Until Cells(iRow, 4) = ""
If Cells(iRow, 4).Value >= StartVal And Cells(iRow, 4).Value < EndVal Then iCnt = iCnt + 1
iRow = iRow + 1
Loop
[K22].Value = iCnt
End Sub

VoG II 20:38 04 Aug 06

Fascinating but the solution doesn't quite fit with your original question which did not suggest that the 'formula' should only apply to cell A23.

Interesting mixture of [R,C] and Cells(iRow,Jrow) notation used in the macro - but if it works then what the heck.

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…