# rounding over a range

#### johngio

##### Board Regular
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

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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

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
Code:
``````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

Replies
2
Views
118
Replies
1
Views
166
Replies
0
Views
128
Replies
0
Views
158
Replies
3
Views
179

1,203,060
Messages
6,053,303
Members
444,650
Latest member
bookendinSA

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back