How to calculate variance between dates with referenced values?

summetg

Hi all,

I tried using the NETWORKDAYS formula and it appears it only works when you have date columns where you enter the date manually. I have a project rollup sheet which I am trying to calculate the variance of NETWORKDAYS (which considers the non-working days) of 2 columns which references dates from another sheet.

I've tried the following:

• Using NETWORKDAYS and referencing these columns
• Matching the value of the column in a helper column
• Created an automation when the column with the referenced date changes, that it updates the helper column. It appears it thinks its a string value and doesn't pickup a change happened to trigger the automation

Any ideas how I can calculate the variance? NETWORKDAYS is preferred as it also considers non-working days configured.

Thanks

Paul Newcome

That shouldn't be the case. Are you able to provide screenshots? Double check your columns are set as date type columns.

summetg

Hi @Paul Newcome

Hoping I can find a fix today for this :)

So here's the formula when I'm copying from the helper columns in the screenshot - I receive the error INVALID DATA TYPE. I am copying the cell from the column to the using the formulas =[Project Due Date]@row and =[Project Planned Due Date]@row. Those helper columns are date columns.

The source columns Project Planned Due Date and Project Due Date are not date columns. I'm not sure if that's the issue? This sheet has over a hundred links to pull dates from other project summary sheets.

Paul Newcome

The source data needs to be a date so that it pulls the rest of the way through as dates. Try changing all columns involved into date type columns (except for the NETWORKDAYS column).

summetg

Hi Paul,

Thanks for the tip. So I did a quick test by referencing dates from another sheet on the helper columns which are defined as dates; it looks like network days worked. I switched between text and dates on these helper columns and it appears the references are stay preserved.

The cells on the columns 'Project Planned Due Date' and 'Project Due Date' are created from projects we provision via SCC. Before I change those columns from Text to Date, would this break any other links or require changes in SCC? The column name is not changing just the column type. I want to avoid having to create over 200 links and feel it should be safe based off the small test I did...

Paul Newcome

Changing a column type should not change a link. It should only change the data type that comes over in the link.

summetg

Tested it out and it worked