rounding over a range

[johngio]

Hi all,

I'm trying to use the following formula to tell me the row number of the cell that matches the first day of the month of the month after the date in cell c2. Here 'tis:

=MATCH(DATE(YEAR(C$2),MONTH(C$2)+1,DAY(1)), 'Sheet2!A20:A10000, 1)

Only problem is, the dates in Sheets2 are entered as follows:

21/3/04 5:00pm

Does anyone know how I can round the 'Sheet2!A20:A10000 value down to the nearest whole integer?

I tried round and rounddown, but they give#value errors, so i assume they don't work over ranges.

Thanks

 

[tactps]

First, your formula is a little off. Try:

=MATCH(DATE(YEAR(C$2),MONTH(C$2)+1,DAY(1)),ROUNDDOWN(Sheet2!A20:A10000,0),0)

Enter this with Ctrl-Shift-Enter

Then you must format your dates and times as:
21/3/04 5:00 pm (ie space before the "pm")
not
21/3/04 5:00pm

 

[SydneyGeek]

John, if you format the cells without the time component does that make any difference?
If not, you could create another column which has the times stripped off.
eg: Dates/Times are in A, you want dates only in P

Sub WholeDates()
   For Each c in Range("B2:B" & Range("B65536").End(xlUp).Row)
     c.Offset(0,15).Value = c.Value\1
   Next c
   Range("P:P").NumberFormat="dd/mm/yyyy"
End Sub

Place this code in a new module, run the code. Then base your match on column P

Denis