Hi all,

I've got a spreadsheet which contains revenue information, from when it is forecast to start and when it is due to end. It gives me the single revenue figure, and what I want to do is split it up over however many months there are. That's easy enough by itself by just using DateDiff and figuring out the months, but the kicker is I need to make it so it takes into account the amount of days in a month.

So for example, there's revenue that covers a period from 12 June 2011 to 18 March 2012. There needs to be a different figure for months with 30 days, 31 days and then February. As well as the first/last months which will be truncated.

Is there an easier way to do this than a whole bunch of select case statements based on start/end date, which is all I can think of at the moment?

Any help would be greatly appreciated.