cell value between to times!!!

  peug417 20:52 31 Jul 2006

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 2006


  johnnyrocker 23:31 31 Jul 2006

access might be better?


  peug417 20:12 04 Aug 2006

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
[K22].Value = iCnt
End Sub

  VoG II 20:38 04 Aug 2006

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.

Elsewhere on IDG sites

WPA2 hack: How secure is your Wi-Fi?

HP’s new Surface Pro rival is designed specifically for Adobe-using designers and artists

Best kids apps for iPhone & iPad

Que faire si son iPhone ou iPad est tombé dans de l'eau ?