Showing Account Balances Nearest to the End of a Month

Hello I'm trying to create a pivot table that displays the balance of an account

The table containing the data will look something like this:

Date.................Account 1..........Account 2
04 May 2010..........200...............435
05 May 2010.............................435345
07 May 2010..........432.........................
08 May 2010..........43534..........4534
09 May 2010.................................345
12 June 2010...........43114
16 June 2010..........3453................534
19 June 2010
21 June 2010..........6343.......... 54


The pivot table will have the dates horizontally across the page (columns). There will be two rows with the account balances.

Now, when I group the dates by month I would like to see the balance of the account, which is closest to the end of the month (not the sum or any other function Excel provides in the value field settings. How do I do this? Any help is much appreciated!!

This is what the pivot table should look like

Date....................May 2010..........June 2010
Account 1..............43534................6343
Account 2................345................. 54