+ Reply to Thread
Results 1 to 2 of 2

Allocating equal cost from start and end date of the range to start and end project date

  1. #1
    Registered User
    Join Date
    06-12-2020
    Location
    India
    MS-Off Ver
    2016
    Posts
    1

    Allocating equal cost from start and end date of the range to start and end project date

    Hi,

    I really need help urgently. I have been struggling from 4 days and I am not able to solve it. I have to find equal distribution of cost in cell G6:S6, in each range. However the cost should auto calculate itself if we change the start date and end date of the project according to no. of days worked. Cost per hour and hours per day i already given.

    There are 13 ranges in the data. Each range equals to 4 weeks which becomes 20 working days (assuming Saturday and Sunday are off). All dates are in MM/DD/YY format.

    Also here the financial period starts from 1st July and ends on 31st May every year. So R1 is from 31st/May/2020 - 27th/june/2020 (cell G2 and G3).

    What i need to do is calculate the cost for each row from start date of joining to end date of joining range wise. For example: If a person joins a project on 10/Aug/2020 and end the project on 29/Dec/2020 so that cost will have to calculated from cells I5-N5, specific to no. of working days in range R3 and R8 from start and end date.

    For simple understanding, as we have 12 months in a year, right now for this we have 13 range, each range equals 4 weeks.

    Can someone please tell me what formula I should put?
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,139

    Re: Allocating equal cost from start and end date of the range to start and end project da

    Hello Athena1030 and Welcome to Excel Forum.
    Please paste the following formula into cell G5 and then drag the fill handle over and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that for this to work the values in rows 2 and 3 must be actual dates. In the file attached to post #1 they are texts that look like dates (paste special multiply by 1 will convert them quickly).
    Note that the date range 10/18/2020 to 11/17/2020 has 22 workdays.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Replies: 0
    Last Post: 11-20-2019, 05:39 AM
  2. Project Start Date and Tasks Start/Finish Time
    By sammyb304 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-11-2019, 09:36 AM
  3. Get the start date and end date of project in a range of Project Allocation
    By juan.doe in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2018, 09:54 AM
  4. [SOLVED] Using min and if formula I would like to get cost based off of the start and end date.
    By David713 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-27-2017, 03:02 PM
  5. Checking if date value in cell is between or equal to start and end date
    By Beckmonkey in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-23-2014, 02:20 PM
  6. Replies: 0
    Last Post: 06-14-2012, 02:49 PM

Tags for this Thread

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