We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

# How to make table cells shaded based on project duration? RSS

## 7 replies

Last post Aug 25, 2019 12:24 PM by mgebhard

• rogersbr

Member

126 Points

878 Posts

### How to make table cells shaded based on project duration?

 2020 qtr 1 2020 qtr 2 2020 qtr 3 2020 qtr 4 2021 qtr 1 2021 qtr 2 Proj 1 1 2 3 4 5 6 Proj 2 7 8 9 10 11 12 Proj 3 13 14 15 16 17 18 Proj 4 19 20 21 22 23 24

Proj 1 start date:  (date)   end date (date)

Proj 2 start date: (date)    end date  (date)

Proj 3 start date: (date)   end date (date)

Proj 4 start date: (date)    end date (date)

Code exists to say:  box1 = true (the cell at #1 is shaded)  box1  = false (the cell is clear or white).  The projects may last 10 years or just 1 year, a project may extend a long ways covering many cells or just cover 1.  Problem: Using the start and end dates of a project, how to express this in a way that shades in cells?  Its quarters so month 1, 2, 3 then 4, 5, 6 etc.

If the start date is Mar 15 2020 and end date is Nov 18 2021 how can that be calculated or? to figure out which cells get shaded?  We can look at it and say oh, q1 of 2020 and every cell until q4 of 2021

If c# would allow variable names to be cast dynamically I could find a solution, like

var box1 = false;

var box2 = false;   etc and then in some other part reference box1 as

int x = 1;

if( (box + x.ToString()) == false) {...}  which can be done in javascript.  Could ASM be dropped in there and use variable name manipulation?

Or is there some way to figure out what cells to shade based on a start and stop date?

thanks

• mgebhard

All-Star

53781 Points

24076 Posts

### Re: How to make table cells shaded based on project duration?

I recommend using a progress bar library.

https://www.w3schools.com/howto/howto_js_progressbar.asp

https://www.w3schools.com/bootstrap/bootstrap_progressbars.asp

The math bits are up to you.  Percent complete is 1 - ((end - start)/[expected duration]).  You'll need to determine the duration units, days, weeks, hours, milliseconds.

• rogersbr

Member

126 Points

878 Posts

### Re: How to make table cells shaded based on project duration?

NO, thats not possible.  Its a hard requirement. must be a Word type table.  If I could put some gantt chart in? it would be done.

So its figure out how to fill in the 'boxes' based on the start/stop dates.  manually.

• mgebhard

All-Star

53781 Points

24076 Posts

### Re: How to make table cells shaded based on project duration?

#### rogersbr

NO, thats not possible.  Its a hard requirement. must be a Word type table.  If I could put some gantt chart in? it would be done.

So its figure out how to fill in the 'boxes' based on the start/stop dates.  manually.

This is a basic math question.  You can find the number if quarters between two dates using the following SO post.

https://stackoverflow.com/questions/6592580/count-of-quarter-between-dates

It is a little easier to calculate the number of quarters between two dates in T-SQL.

https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017

Once you have the quarters then loop over the table columns in C# or JavaScript and add background color.  You know the start and end dates so so all you need to do is count the columns.  This would be a nested loop.  The outer loop loops over the rows.  The inner loop loops over the columns.

My first response illustrates how to set the percentage of a element using simple CSS and JavaScript if you need to express the first or last column as a percentage.

IMHO, it seems much easier to do this using <div> than an HTML table.  Why does this report need to be a Word Style table?  The <div> approach is response and because it works with percentages and render properly on different screen resolutions.

• rogersbr

Member

126 Points

878 Posts

### Re: How to make table cells shaded based on project duration?

#### rogersbr

NO, thats not possible.  Its a hard requirement. must be a Word type table.  If I could put some gantt chart in? it would be done.

So its figure out how to fill in the 'boxes' based on the start/stop dates.  manually.

This is a basic math question.  You can find the number if quarters between two dates using the following SO post.

https://stackoverflow.com/questions/6592580/count-of-quarter-between-dates

It is a little easier to calculate the number of quarters between two dates in T-SQL.

https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017

Once you have the quarters then loop over the table columns in C# or JavaScript and add background color.  You know the start and end dates so so all you need to do is count the columns.  This would be a nested loop.  The outer loop loops over the rows.  The inner loop loops over the columns.

My first response illustrates how to set the percentage of a element using simple CSS and JavaScript if you need to express the first or last column as a percentage.

IMHO, it seems much easier to do this using <div> than an HTML table.  Why does this report need to be a Word Style table?  The <div> approach is response and because it works with percentages and render properly on different screen resolutions.

public static int getQuartersBetween(System.DateTime d1, System.DateTime d2)
{
return (int) Microsoft.VisualBasic.DateAndTime.DateDiff(DateInterval.Quarter, d1, d2);
}

Just had to add references to the project, it failed because it cant convert Long to Int, so forced it.

The output is a Word document that has to be built from scratch with custom formatting and alot of other tables.  the rest of it works, I could use Javascript to concatenate the variable name and reference it, but c# wont allow it, so i was stuck

with Word doc built from scratch + using a template its over 50k LOC for one page, but it works.  DateInterval.Quarter ? didnt realize that was there thanks

• rogersbr

Member

126 Points

878 Posts

### Re: How to make table cells shaded based on project duration?

Is it possible in c#

bool box1 = false

bool box2 = false

bool box3 = false

for( int x = 1; x<4; x++) {

box + x.ToString() = true;

}

result:

box1 = true;

box2 = true;

box3 =true; how can you create a variable name then execute it?

• rogersbr

Member

126 Points

878 Posts

### Re: How to make table cells shaded based on project duration?

Once you have the quarters then loop over the table columns in C# or JavaScript and add background color.  You know the start and end dates so so all you need to do is count the columns.  This would be a nested loop.  The outer loop loops over the rows.  The inner loop loops over the columns.

Really? how??

In in Q3 of the first year.  The count is 5. so from Q3's cell  (box3) shade that one plus 8 more.  HOW??  How can you combine values & reference in c# ?

Number of Quarters = 5

if(month >=7 && month <=9) {

// were now in the 3rd qtr, color in X more?

for ( int x = numof Quarters; x>0; x--)

{

box + x = true;   // the variable name is called box1, box2, box3 etc

}

but c# wont allow this kind of reference. So how to loop thru?  The ONLY way I know of is to create a zillion functions with every possible combination, 100's or more? and you call the one that matches. That cant be the best there is.  It will take weeks to write all that out.

To set a cells shade you just say box1 = true;  or to clear it box1 = false;

}

• mgebhard

All-Star

53781 Points

24076 Posts

### Re: How to make table cells shaded based on project duration?

There are no limitations to what you can design and build to solve this programming problem.

Below you'll find a basic example.  The high level idea is to get a list of quarters (start and end dates) for the period you want to view in the UI.  These are the table columns.  Then loop over the columns and compare the project begin and end dates to each of the column bounds to figure out of the project falls within the column.  If the project timeline is within the column start and end date then logically mark the item.  Now you can use the List<TimeSpanItem> to populate an HTML table.

A couple of comments.  This is not production code.  The code sample is meant as an example to get you started and it does not show how to loop over a collection of project.  That bit is left up to you.  Feel free to update the code any way you like or design your own path.

{
class Program
{

static void Main(string[] args)
{
//Set up the main timeline for the table
DateTime tableStart = new DateTime(2020, 1, 1);
DateTime tableEnd = new DateTime(2022, 1, 1);

List<TimeSpanItem> tableQuaters = PopulateQuarterTimeSpans(tableStart, tableEnd);

foreach (TimeSpanItem col in tableQuaters)
{
Console.WriteLine(col.ToString());
}

Console.WriteLine();
Console.WriteLine("------------------------------------");
Console.WriteLine();

//Compare the main time line to the project timeline
DateTime ProjStart = new DateTime(2020, 4, 1);
DateTime ProjEnd = new DateTime(2020, 9, 30);

List<TimeSpanItem> projectQuaters = new List<TimeSpanItem>();

foreach (TimeSpanItem col in tableQuaters)
{
if(ProjStart <= col.StartDate & ProjEnd >= col.EndDate)
{
}
else
{
}
}

foreach (TimeSpanItem col in projectQuaters)
{
Console.WriteLine(\$"{col.ToString()} ({!TimeSpanItem.IsNullOrEmpty(col)})");
}

}

//Get a list of TimeSpans between two dates
public static List<TimeSpanItem> PopulateQuarterTimeSpans(DateTime TableStart, DateTime TableEnd)
{
List<TimeSpanItem> quarters = new List<TimeSpanItem>();
TimeSpanItem temp = new TimeSpanItem();

DateTime monthPointer = TableStart;

while (monthPointer <= TableEnd)
{
if (monthPointer.Month % 3 == 0)
{
temp = new TimeSpanItem()
{
};
}
}
return quarters;
}
}

public class TimeSpanItem
{
public DateTime StartDate { get; set; }
public DateTime EndDate { get; set; }

public TimeSpanItem()
{
StartDate = DateTime.MinValue;
EndDate = DateTime.MinValue;
}

public TimeSpanItem(DateTime startDate, DateTime endDate)
{
StartDate = startDate;
EndDate = endDate;
}

public static bool IsNullOrEmpty(TimeSpanItem item)
{
return item.StartDate == DateTime.MinValue & item.EndDate == DateTime.MinValue;
}

public override bool Equals(object obj)
{
TimeSpanItem temp = (TimeSpanItem)obj;
return (StartDate == temp.StartDate & EndDate == temp.EndDate);
}

public override int GetHashCode()
{
return base.GetHashCode();
}

public override string ToString()
{
return \$"{StartDate.ToString("MM/dd/yyyy")} - {EndDate.ToString("MM/dd/yyyy")}";
}
}

}