AnsweredAssumed Answered

Calculation Help!  Let function? Custom Function?

Question asked by user5571 on Jan 15, 2016
Latest reply on Jan 17, 2016 by mikebeargie

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!


Outcomes