+ Reply to Thread
Results 1 to 4 of 4

Allocating costs dependant on date input

  1. #1
    Registered User
    Join Date
    06-01-2020
    Location
    North Lincolnshire, England
    MS-Off Ver
    Micorsoft Office 365 Pro Plus
    Posts
    2

    Allocating costs dependant on date input

    Sample Uploaded

    Hi All apologies,

    In the attached sample i need to be able to put in the dates off and on and then under each month this will populate how much is to be allocated to that month.

    For example on my sample the 1st item Signs Cones & Equipment will be on hire from 04/01/20 untill 20/05/20 at a rate of 20/week

    Jan will therefore be 4 weeks at 20/week and so on until May which as it ends on 20/05/20 there will be 2.6 weeks so under May there will be 52 of costs allocated

    Thanks again
    Attached Files Attached Files
    Last edited by Mchale91; 06-01-2020 at 05:53 AM. Reason: Upload Sample

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Allocating costs dependant on date input

    HOW TO ATTACH YOUR SAMPLE WORKBOOK:

    Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,989

    Re: Allocating costs dependant on date input

    I agree attach a spreadsheet, and suppect you will wish to add holidays
    you want the working days between (the maximum of the start of the months and the start date for the activty) and the minimum of the start of the next month and the end of the activity. If the dates are outside this range, the answer is 0


    =MAX(0,NETWORKDAYS(MAX(C$1,$A2),MIN($B2,D$1)))*21.84


    where C1 1/1/20
    d1 1/2/20
    A2 19/1/20
    B2 4/3/20
    21.84 is daily rate

  4. #4
    Registered User
    Join Date
    06-01-2020
    Location
    North Lincolnshire, England
    MS-Off Ver
    Micorsoft Office 365 Pro Plus
    Posts
    2

    Re: Allocating costs dependant on date input

    Cheers davsth your formula works!

    Much appreciated

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Allocating Contract Costs by Fiscal Month
    By mps1228 in forum Excel General
    Replies: 5
    Last Post: 08-17-2018, 08:39 AM
  2. [SOLVED] finding average with date dependant and cell dependant
    By sfoll in forum Excel General
    Replies: 4
    Last Post: 08-24-2015, 04:58 AM
  3. Allocating costs - one row to mulitple rows
    By governmentworker in forum Excel General
    Replies: 5
    Last Post: 04-05-2015, 04:36 AM
  4. Allocating activity according to date.
    By sipa in forum Excel General
    Replies: 3
    Last Post: 12-28-2014, 02:13 PM
  5. Allocating Costs by Dynamic Weighted Average
    By yoman987 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-15-2014, 01:35 PM
  6. [SOLVED] Allocating a given date to a date range bucket: futures contract expiry
    By labogola in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-26-2013, 08:50 AM
  7. macro to spread costs dependant on criteria
    By mkmed in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-06-2007, 01:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1