Due Date Summary based on multi-sheet spreadsheet

jthomas666 · #10 03-17-2020, 07:37 AM

OK, I'm about to the point where I have my head wrapped around the problem...still don't know how to fix it, as my Excel skills are limited.

Here's the relevant section from your post explaining the infrastructure of the sheet:

Quote:

About DueDate calculation:
In my example, there was only 2 calculation rules (2 due types). I implemented a formula, where for every different due type due date is calculated (= DueDate1 + DueDate2 + ...) , and it may have value 0 (i.e. this due type is not applied) or value > 0 (i.e. this due type is applied and conditions for calculation are filled). Those due dates for every due type are then summarised. For every entry in table, only one of those partial dates may have value > 0. As result, the formula returns 0 when no condition for due date calculation is filled, or it returns a due date for due type which can be applied.
When you have more than 2 due types, then probably it will be best to have a helper column for every due type separately. Then DueDate is calculated like
Code:
=IF([SUM(@DueDate1], @DueDate1], ...)=0;"";[SUM(@DueDate1], @DueDate1], ...))
Partial formolas for both due types in my example are
Code:
=([@DueType]="WD")*WORKDAY([@BaseDate];[@DueValue];tHolidays[Holiday])
Code:
= ([@DueType]="CD")*([@BaseDate]+[@DueValue]+SIGN([@DueValue])*COUNTIFS(tHolidays[Holiday];">=" & MIN([@BaseDate];[@BaseDate]+[@DueValue]);tHolidays[Holiday];"<=" & MAX([@BaseDate];[@BaseDate]+[@DueValue]);tHolidays[IsWeekend];FALSE))
To have initial due date calculation(s) in helper column(s) is because date 0 in Excel equals with January 1st 1900, but you need an empty string instead. When you have a long formula 2 times in same cell, then it will be a bit messy.

So, the next steps are

1. determine the formula for calculating the due date for the new doc class. I *think* the formula is

([@DueType]=”SA”)*WORKDAY(EOMONTH(BaseDate)+1,-1),@DueValue])

2. Set up helper columns to generate the due dates for each of the three due types.

3. Populate the main DueDate field by creating a formula that looks at the three helper columns and taking the non-zero value.

Am I ANYWHERE in the right neighborhood?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Summriez all worksheet in summary work sheet	MILADREZAEE	Excel	7	05-21-2018 05:01 AM
How to calculate summary progress date time on summary tasks	Logotip	Project	0	05-16-2017 01:18 AM
Creating a dynamic summary sheet	FenelonPaul	Excel	5	09-23-2015 07:11 AM
Can a Data Source be one sheet in a multi-sheet Workbook	nfotx	Mail Merge	1	07-01-2015 12:55 AM
Construct a summary sheet by summing up from one or more than one sheet.	PRADEEPB270	Excel	1	11-04-2011 03:46 AM