Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-02-2020, 09:19 AM
jthomas666 jthomas666 is offline Due Date Summary based on multi-sheet spreadsheet Windows 10 Due Date Summary based on multi-sheet spreadsheet Office 2016
Competent Performer
Due Date Summary based on multi-sheet spreadsheet
 
Join Date: Jun 2016
Location: Warner Robins, GA
Posts: 171
jthomas666 will become famous soon enough
Default Due Date Summary based on multi-sheet spreadsheet

I'm designing the attached spreadsheet to track due dates for a variety of reports. There are actually even more than are on the spreadsheet at the moment.



In most cases, the due date for the document is dependent on some other factor--project approval, release date, etc. I've got it set up so that as the various trigger dates are added, the due dates are updated.

There's a Summary tab on the worksheet. What I would like is to have that tab have a rolling display that always lists the deliverables that are due over the next 10 business days.

I have no idea if it's possible, let alone how to do it.

Any suggestions?
Attached Files
File Type: xlsx CDRL Tracking.xlsx (38.8 KB, 16 views)
Reply With Quote
  #2  
Old 03-03-2020, 02:03 AM
ArviLaanemets ArviLaanemets is offline Due Date Summary based on multi-sheet spreadsheet Windows 8 Due Date Summary based on multi-sheet spreadsheet Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Your current design means, that whenever you add a new class of documents, you have to add a new worksheet, and redesign your reports.

Better design is, where all your main data entry (document register) is in single table, with additional tables to define document classes and calculation rules. Then when you need to add a new document class, you simply define it, and the you can immediately register the document of new class without any changes in design of your application.

Added is an example workbook which illustrates this approach.

Document classes are defined in table on sheet DocClasses. Calculation rules for every class are determined by TypeCode.

TypeCodes are defined in table on sheet DueTypes.

In table on sheet Documents, columns with differently colored headers contain formulas. Columns with dark green headers are meant to display info for users. Columns with brown headers are helper columns used for calculations, and may be set hidden.
When document is delivered, it may be delivered On Time, or not On Time (Overtime). When document is not delivered jet, it may be Overtime (OnTime= FALSE), or it's due date is later than today.

At least most of reporting you wanted, you can get simply using autofilter on documents table. You also can create any number of different report sheets - when all data is in single table, then this is much easier. When you need some report you aren't able to figure out, post here what you want.

You can freely rename worksheets, Table headers, and defined Tables - all formulas in workbook will be updated automatically.
Attached Files
File Type: xlsx DueDate.xlsx (17.0 KB, 22 views)
Reply With Quote
  #3  
Old 03-04-2020, 05:44 AM
jthomas666 jthomas666 is offline Due Date Summary based on multi-sheet spreadsheet Windows 10 Due Date Summary based on multi-sheet spreadsheet Office 2016
Competent Performer
Due Date Summary based on multi-sheet spreadsheet
 
Join Date: Jun 2016
Location: Warner Robins, GA
Posts: 171
jthomas666 will become famous soon enough
Default

Good Lord, that's incredible!

Thank you so much!
Reply With Quote
  #4  
Old 03-04-2020, 01:39 PM
jthomas666 jthomas666 is offline Due Date Summary based on multi-sheet spreadsheet Windows 10 Due Date Summary based on multi-sheet spreadsheet Office 2016
Competent Performer
Due Date Summary based on multi-sheet spreadsheet
 
Join Date: Jun 2016
Location: Warner Robins, GA
Posts: 171
jthomas666 will become famous soon enough
Default

Also, could you point me to a document that goes over how to set this kind of stuff up?

Thanks again!
Reply With Quote
  #5  
Old 03-06-2020, 07:58 AM
ArviLaanemets ArviLaanemets is offline Due Date Summary based on multi-sheet spreadsheet Windows 8 Due Date Summary based on multi-sheet spreadsheet Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by jthomas666 View Post
Also, could you point me to a document that goes over how to set this kind of stuff up?
What do you mean by it?

I have used defined Tables in my example. You can define a table as Table, selecting any field in regular excel table (which must have column headers btw.), and then clicking Table in INSERT menu. You can name any created Table with unique Name after that.

Defining a Table enables DESIGN menu whenever any table element (field, column, row, header, etc.) is selected.

In defined Table, you can use specific Table syntax for all Excel formulas, and anywhere in Excel workbook you can refer to specific Table elements in formulas (in same Table, or in any Table in your workbook). E.g. when you have defined Table and named it tMyTable, then you can:

1. refer to whole datarange of your Table, like
Code:
=INDEX(tMyTable,1,1)
2. refer to whole column in datarange of your table, like
Code:
=SUM(tMyTable[ColumnHeaderValue])
3. refer to cell in any column of tMyTable in same row as your formula is placed, like
Code:
=tMyTable[@ColumnHeaderValue]
etc. (When you refer to some element in same Table, you can omit Table name.)

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.

Of course you can make a next step and use dynamic names instead of invisible helper columns, but this takes the complexity to entirely new level.
Reply With Quote
  #6  
Old 03-12-2020, 07:34 AM
jthomas666 jthomas666 is offline Due Date Summary based on multi-sheet spreadsheet Windows 10 Due Date Summary based on multi-sheet spreadsheet Office 2016
Competent Performer
Due Date Summary based on multi-sheet spreadsheet
 
Join Date: Jun 2016
Location: Warner Robins, GA
Posts: 171
jthomas666 will become famous soon enough
Default

There appears to be a problem with the formula determining whether a doc has been delivered on time.

Three days ago I submitted some docs on time. I entered the date in the spreadsheet and the On Time flag correctly displayed TRUE.

I opened up the spreadsheet today and the same flag showed FALSE (see attachment). I hadn't changed any data; the only thing that is different is that the due date is now in the past.

Based on testing, if the due date is in the past or is the current date, the flag displays as FALSE even if the doc was submitted on time. The formula

=IF(AND([Delivered]=0,[DueDate]>TODAY()),"",AND([Delivered]>0,[DueDate]>TODAY()))

looks like it's using the current date for the comparison, instead of just comparing the delivered date to the due date. I don't understand Excel formulas well enough to edit the thing. What I need is basically

If [DueDate]-[Delivered]=>0, True, Else False

TIA
Reply With Quote
  #7  
Old 03-12-2020, 07:54 AM
jthomas666 jthomas666 is offline Due Date Summary based on multi-sheet spreadsheet Windows 10 Due Date Summary based on multi-sheet spreadsheet Office 2016
Competent Performer
Due Date Summary based on multi-sheet spreadsheet
 
Join Date: Jun 2016
Location: Warner Robins, GA
Posts: 171
jthomas666 will become famous soon enough
Default

OK, I've gotten a formula

=IF([@DueDate]>=[@Delivered], "TRUE","FALSE")

that seems to be working.
Reply With Quote
  #8  
Old 03-12-2020, 08:10 AM
ArviLaanemets ArviLaanemets is offline Due Date Summary based on multi-sheet spreadsheet Windows 8 Due Date Summary based on multi-sheet spreadsheet Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

OK. The formula for OnTime was faulty! Right one must be
Code:
=IF(AND([Delivered]=0,[DueDate]>TODAY()),"",AND([Delivered]>0,[Delivered]<=[DueDate]))
I.e. when:
1. Document is not delivered and due date is in future, then it is not late jet and OnTime field will be empty;
2. Document is not delivered and due date is today or earlier, then it is late or it is being late when not delivered immediately;
3. Document is delivered, and due date was later or equal than delivery date, then delivery was on time;
4. Document is delivered, but due date was earlier than delivery date, then delivery was late.
Reply With Quote
  #9  
Old 03-16-2020, 10:07 AM
jthomas666 jthomas666 is offline Due Date Summary based on multi-sheet spreadsheet Windows 10 Due Date Summary based on multi-sheet spreadsheet Office 2016
Competent Performer
Due Date Summary based on multi-sheet spreadsheet
 
Join Date: Jun 2016
Location: Warner Robins, GA
Posts: 171
jthomas666 will become famous soon enough
Default

OK, I've got another request.

I have a new report to add to the spreadsheet, a semi-annual report. The specifics vary on the due date, but the way I would like it to work is this:

1. Enter any date in a month as the BaseDate
2. The system returns the last workday of the month as the due date.

I've set a new DocClass as follows:

DocClass - Semi-annual
DueType - WD
Due Value - EndofMonth
ClassComment - BaseDate = any day in target month

I've set the DueType as follows
TypeCode - SA
DueType - Semi-annual
comment - DueDate =WORKDAY(EOMONTH(BaseDate,0)+1,-1) [formula I found online]

Because I still don't fully understand the spreadsheet, I haven't been able to make the above work within your setup; i get a value error.

If I just enter the formula directly into the spreadsheet, it calculates correctly for that cell; however, it also applies the same formula to the rest of the due dates.


So clearly I am doing something wrong. I don't know if I'm missing something behind the scenes.


Any ideas?

thanks
Reply With Quote
  #10  
Old 03-17-2020, 07:37 AM
jthomas666 jthomas666 is offline Due Date Summary based on multi-sheet spreadsheet Windows 10 Due Date Summary based on multi-sheet spreadsheet Office 2016
Competent Performer
Due Date Summary based on multi-sheet spreadsheet
 
Join Date: Jun 2016
Location: Warner Robins, GA
Posts: 171
jthomas666 will become famous soon enough
Default

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?
Attached Files
File Type: xlsx DueDate-test.xlsx (25.9 KB, 7 views)
Reply With Quote
  #11  
Old 03-18-2020, 12:57 AM
ArviLaanemets ArviLaanemets is offline Due Date Summary based on multi-sheet spreadsheet Windows 8 Due Date Summary based on multi-sheet spreadsheet Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Almost

I designed your test file for multiple due types.
Attached Files
File Type: xlsx DueDate-test.xlsx (26.1 KB, 7 views)
Reply With Quote
  #12  
Old 03-18-2020, 05:28 AM
jthomas666 jthomas666 is offline Due Date Summary based on multi-sheet spreadsheet Windows 10 Due Date Summary based on multi-sheet spreadsheet Office 2016
Competent Performer
Due Date Summary based on multi-sheet spreadsheet
 
Join Date: Jun 2016
Location: Warner Robins, GA
Posts: 171
jthomas666 will become famous soon enough
Default

You, sir, are a scholar and a gentleman!
Reply With Quote
  #13  
Old 03-19-2020, 12:25 PM
jthomas666 jthomas666 is offline Due Date Summary based on multi-sheet spreadsheet Windows 10 Due Date Summary based on multi-sheet spreadsheet Office 2016
Competent Performer
Due Date Summary based on multi-sheet spreadsheet
 
Join Date: Jun 2016
Location: Warner Robins, GA
Posts: 171
jthomas666 will become famous soon enough
Default

OK, last question for at least a day or two.

We had to change the DueDate calculation from

Quote:
=IF(AND([Delivered]=0,[DueDate]>TODAY()),"",AND([Delivered]>0,[DueDate]>TODAY()))
to

Quote:
=IF(AND([Delivered]=0,[DueDate]>TODAY()),"",AND([Delivered]>0,[Delivered]<=[DueDate]))
I just pasted the new formula into the appropriate cells. When adding some records today, I noticed that the new records had the old formula, suggested that the formula is set as a default somewhere else. How do I change that default?

Again, I cannot tell you how much I appreciate your help. You'll be happy to know that I added several new DocClasses today without problem, so I'm making progress, slow as it is.

Thanks again.

Jim
Reply With Quote
  #14  
Old 03-20-2020, 12:24 AM
ArviLaanemets ArviLaanemets is offline Due Date Summary based on multi-sheet spreadsheet Windows 8 Due Date Summary based on multi-sheet spreadsheet Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

When pasting the formula, the automatic formula correction for Table column may be not 'kick in'. Copy a cell with right formula, and PasteSpecial.Formulas to entire Table column.
Reply With Quote
  #15  
Old 03-26-2020, 09:47 AM
jthomas666 jthomas666 is offline Due Date Summary based on multi-sheet spreadsheet Windows 10 Due Date Summary based on multi-sheet spreadsheet Office 2016
Competent Performer
Due Date Summary based on multi-sheet spreadsheet
 
Join Date: Jun 2016
Location: Warner Robins, GA
Posts: 171
jthomas666 will become famous soon enough
Default

Well, I did last over a week...

My boss keeps changing requirements on me. Actually, I probably should have seen this issue coming.

Right now, the dates are listed as shown in Date1



I need what is shown in Date2



I've tried moving the columns, but that breaks the formula. I've tried moving the entire column, just the part that has data, but nothing works.

So, how do I move the columns? There's a 50/50 chance boss will want the due date as the first column of the spreadsheet (trying to talk him out of it), so please explain the process.

I've included the current spreadsheet (minus data), in case I screwed it up somewhere else.

thanks,
Jim
Attached Images
File Type: png Date1.png (6.4 KB, 43 views)
File Type: png Date2.png (4.5 KB, 41 views)
Attached Files
File Type: xlsx DueDate-test.xlsx (32.1 KB, 8 views)
Reply With Quote
Reply

Thread Tools
Display Modes


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
Due Date Summary based on multi-sheet spreadsheet Creating a dynamic summary sheet FenelonPaul Excel 5 09-23-2015 07:11 AM
Due Date Summary based on multi-sheet spreadsheet Can a Data Source be one sheet in a multi-sheet Workbook nfotx Mail Merge 1 07-01-2015 12:55 AM
Due Date Summary based on multi-sheet spreadsheet Construct a summary sheet by summing up from one or more than one sheet. PRADEEPB270 Excel 1 11-04-2011 03:46 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:19 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft