Date Issues

[Smurlos]

Hi

Please find attached a test spreadsheet I'm working on.

There's a sheet called data which stores payments made, the date it was made, who made it and how much the payment was.

The 2nd sheet is a report. If you click on the generate report button and put in your from and to dates, it is meant to only show payments made for that date range.

You click the generate report button and it displays a userform.
If I type in from as 01/06/2010 and to as 10/06/2010 and click on the generate report button, it shows the report page.

It is flipping the date around to "06/01/2010" to "06/10/2010".
I then convert the date to text string so it gives a string of numbers.

The search works fine based on the number string, so ideally what I want is for excel NOT to change this to 06/10/2010 but keep it as 10/06/2010.

If I change the cell to "text" instead of "date" it will not convert the date to numbers and hence my search does not work whatsoever.

How can I stop excel flipping the date around???? It is REALLY frustrating me!!!

Thanks in advance

[Smurlos]

Is there a way I can convert the date in the input boxes in the macro to the 40xxx number directly via the macro rather than inputting into excel and then getting excel to do this as this would avoid mixing up the formats?

[royUK]

I would have thought a PivotTable would be better than VBA

[Smurlos]

Don't worry, I worked it out in the end.

Please Login or Register  to view this content.

I've then had to store all my dates in serial in the database and do a search that way and then just convert from serial to date when a formatted date is needed.

The reason i can't use pivot tables is it's for work colleagues who are not very computer friendly. They need a simple system where they type a date in this box, a date in this box, click and it prints out a report that's easy to read and understand.