Forum Discussion

jen8080's avatar
jen8080
Helper I
5 years ago

Spread value across time between 2 dates

Hello-

 

I need to be able to determine the number of months betwen the start and end date and show how many employees I need to per month to fill these requested hours. The requested hours should be distrbuted evenly across the start and end dates. 

Each employee is expected to work 50 hours lets say. 

 

Here is just a sample of data to help:

Project IDRecord EntryRequested HoursWork End DateWork Start Date
Project 1144664Wednesday, December 23, 2020Wednesday, December 16, 2020
Project 11446620Wednesday, December 23, 2020Wednesday, December 09, 2020
Project 21399012Monday, August 31, 2020Wednesday, June 10, 2020
Project 21399050Monday, August 31, 2020Wednesday, June 10, 2020
Project 213990175Monday, August 31, 2020Wednesday, June 10, 2020
Project 213990692Monday, August 31, 2020Wednesday, June 10, 2020
Project 21399040Monday, August 31, 2020Wednesday, June 10, 2020
Project 213990125Monday, August 31, 2020Wednesday, June 10, 2020
Project 21399020Monday, August 31, 2020Wednesday, June 10, 2020
Project 31358610Thursday, March 31, 2022Tuesday, March 10, 2020
Project 4141251Monday, August 31, 2020Friday, July 31, 2020
Project 4141256Monday, August 31, 2020Monday, July 27, 2020
Project 4141256Monday, August 31, 2020Monday, July 27, 2020
Project 4141254Monday, August 31, 2020Monday, July 27, 2020
Project 41456119Friday, February 26, 2021Monday, February 08, 2021
Project 41425814.5Friday, January 08, 2021Thursday, October 01, 2020
Project 41425825Friday, January 08, 2021Thursday, October 01, 2020
Project 41425853.5Friday, January 08, 2021Thursday, October 01, 2020
Project 41425818Friday, January 08, 2021Thursday, October 01, 2020
Project 41425818Friday, January 08, 2021Thursday, October 01, 2020
Project 41456119Friday, February 26, 2021Monday, February 08, 2021
Project 41456166.5Friday, February 26, 2021Monday, February 08, 2021

 

Need Help

5 Replies

  • xMikeDx's avatar
    xMikeDx
    Frequent Visitor
    5 years ago

    your time req in months is not going to fly when you are looking at partial months especially at both ends.

    i believe you need to use datediff to get the count of days filter out any non workdays then determin how many hours per day each row is projected to meet your est. 50.

     

    this is psudeo code.

    measure =: 

    var __hours = 50

    var __dayrange = calculate(datediff (facttable[start_date], facttable[end_date], Day),'datetable'[isweekend]=false)

    var __result = divide( __dayrange, __hours)

    return __result

     

    something like this

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    1. Why are there multiple rows for the same Project ID and Record Entry combination?
    2. As an example, for the first 2 rows, how would you calculate the number of months?
    3. Also, for project 4 and record entry 14258, how would you calculate the number of months?
    • jen8080's avatar
      jen8080
      Helper I
      5 years ago

      1. This is just a sample sample of a much larger confidential dataset. Each study can have several requests for more hours to be added.

      2.Again small  sample of the acutal dataset so we could use days if months aren't possible.

      3. above is also applicable for this.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi jen8080,

        I still not so clear for your scenario, can you please share more details about these?

        How to Get Your Question Answered Quickly 
        #1, Did these request hours means the daily works hour that assigns in the date ranges?

        #2, For the records with the same 'project id' and 'Record Entry', did these need to be summarized or calculate standalone?

        #3, Since you are talking about working hours, how the weekend(workday range) configured in your date range?

        Regards,
        Xiaoxin Sheng