Advice Required: Data Spanning Months

[Lee_Mather]

Does anyone have any tips on how I can split a data field into two seperate months

I have a start date and an end date.  Where the start and end date cross into 2 seperate months I need to pro rata the data into each month

For Example

Start Date.......End Date........Total Amount

25th Feb 16....9th Mar 16......11

The issue is, is that the Total Amount is working days, and there are some weekends to take into consideration.

EDIT UPDATE

Thinking it through I think I need a DATEDIFF that excludes weekends.

Any help and advice, very gratefully recieved

ta

Lee

[Lee_Mather]

I found the answer, work with the date table, add a weekday counter, 1 for weekdays, 0 for weekends and bank holidays then do a sum calculation based on the start and end date.

Thanks to Rob Collies blog post for this heads up.

Lee