6 Replies Latest reply on Jan 17, 2016 10:20 AM by mikebeargie

    Calculation Help!  Let function? Custom Function?

    user5571

      I need some advice/help for some calculations fields.

       

      I have 3 fields...

      Hours (value of 16)

      DaysUntilDeadline (value of 11)

      HoursPerDay = Hours/DaysUntilDeadline (value of 1.45)

       

      Then I want 35 calculation fields that populate the HoursPerDay.  Something like...

       

      WorkloadHours Day 01 = 1.45    (HoursPerDay)

      WorkloadHours Day 02 = 1.45    (if DaysUntilDeadline >= 2,HoursPerDay, 0)

      WorkloadHours Day 03 = 1.45    (if DaysUntilDeadline >= 3,HoursPerDay, 0)

      WorkloadHours Day 04 = 1.45    (if DaysUntilDeadline >= 4,HoursPerDay, 0)

      WorkloadHours Day 05 = 1.45    etc

      WorkloadHours Day 06 = 1.45

      WorkloadHours Day 07 = 1.45

      WorkloadHours Day 08 = 1.45

      WorkloadHours Day 09 = 1.45

      WorkloadHours Day 10 = 1.45

      WorkloadHours Day 11 = 1.45

      WorkloadHours Day 12 = 0

      WorkloadHours Day 13 = 0

      WorkloadHours Day 14 = 0

      ....

      WorkloadHours Day 35 = 0


      That would be easy enough but I have have to EXCLUDE Saturday, Sundays, and Public Holidays (Public Holidays would be stored in another table that list the holiday date).  Each of those WorkloadHour days will correspond to a global date field (gDate 01, gDate 02, gDate03, etc). 


      So for example, let's assume the following dates and let's assume 1/29 is a public holiday...


      gDate01 = 1/16/16 (Sat)

      gDate02 = 1/17/16 (Sun)

      gDate03 = 1/18/16 (Mon)

      gDate04 = 1/19/16 (Tue)

      gDate05 = 1/20/16 (Wed)

      gDate06 = 1/21/16 (Thr)

      gDate07 = 1/22/16 (Fri)

      gDate08 = 1/23/16 (Sat)

      gDate09 = 1/24/16 (Sun)

      gDate10 = 1/25/16 (Mon)

      gDate11 = 1/26/16 (Tue)  ** HOLIDAY

      gDate12 = 1/27/16 (Wed)

      gDate13 = 1/28/16 (Thr)

      gDate14 = 1/29/16 (Fri)

      gDate15 = 1/30/16 (Sat)

      gDate16 = 1/31/16 (Sun)

      gDate17 = 2/1/16 (Mon)

      gDate19 = 2/2/16 (Tue)

      gDate20 = 2/3/16 (Wed)

      gDate21 = 2/4/16 (Thr)

      gDate22 = 2/5/16 (Fri)


      the desired result would be


      WorkloadHours Day 01 = 0  

      WorkloadHours Day 02 = 0   

      WorkloadHours Day 03 = 1.45 

      WorkloadHours Day 04 = 1.45  

      WorkloadHours Day 05 = 1.45   

      WorkloadHours Day 06 = 1.45

      WorkloadHours Day 07 = 1.45

      WorkloadHours Day 08 = 0

      WorkloadHours Day 09 = 0

      WorkloadHours Day 10 = 1.45

      WorkloadHours Day 11 = 0

      WorkloadHours Day 12 = 1.45

      WorkloadHours Day 13 = 1.45

      WorkloadHours Day 14 = 1.45

      WorkloadHours Day 15 = 0

      WorkloadHours Day 16 = 0

      WorkloadHours Day 17 = 1.45

      WorkloadHours Day 18 = 1.45

      WorkloadHours Day 19 = 0

      WorkloadHours Day 20=  0

      WorkloadHours Day 21=  0

      WorkloadHours Day 22=  0

      etc etc


      Any ideas or advice on how I can achieve this?    Thanks!