Forum Discussion

Lee_Mather's avatar
Lee_Mather
Regular Visitor
10 years ago

Advice Required: Data Spanning Months

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

Tips and Tricks
Tutorial Requests

1 Reply

  • Lee_Mather's avatar
    Lee_Mather
    Regular Visitor
    10 years ago

    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