Advanced filter by date range, international date format problem

Hi

I am trying to make a spreadsheet to track patients in a surgical unit.

Every patient entered will have a category assigned (from a drop-down list) and a date of surgery.

I have created an Advanced Filter which I want to display entries on a separate worksheet. I want to be able to display all patients of a particular category (eg. Trauma) who had surgery within a certain date range.

I know I can do this with an AutoFilter, but want it to be more straightforward for staff to use by just entering the start and end dates into the Criteria cells for the Advanced Filter. I have the Advanced filter working, and have made it auto-refresh whenever the criteria are modified.

The problem I have is that the dates are being interpreted as a mm/dd/yyyy format, whereas I want to enter them as dd/mm/yyyy format.

I have tried formatting the cells that the date criteria are entered into as "Date" of dd/mm/yyyy format, but it still interprets the dates as mm/dd/yyyy (ie. Interprets 10/08/2013 as 8th October, instead of 10th August).

Is there a way I can ensure the Advanced Filter knows what date format I'm using?

Thanks!
Brent

Hi Norie

Thanks for your reply.

I have all of my data on one worksheet. On a separate worksheet I have the criteria for the Advanced filter. The criteria are "Date" (for start date), "Date" (for end date), and "Category". The advanced filter results are displayed on the worksheet with the criteria (not filtered in place on the main worksheet)

I enter the criteria all on one row so that all criteria must be met. Ie. >start date AND <end date AND category. In category I have entered ="=Facial Trauma"

Everything is working fine, except the date filtering is interpreting the dates as US format, so my results aren't correct. Other parts of this spreadsheet are using dates, and are working as dd/mm/yyyy format (which is the windows system format on my computer).

Basically, I need to know how to make the advanced filter recognise the dates are given as dd/mm/yyyy not mm/dd/yyyy.

Thanks!