Duration Formula Help

ksabrin

Hello, I am trying to create two duration formulas. One is the duration is between request date and manager approval date and the other is the duration between manager approval date to admin approval date. All three of the date columns are the date column type. The date columns are filled in automatically by Workflows.

I need these formulas to also calculate in the company holidays and weekends so that it doesn't inflate the metrics. I do not want to utilize the Gantt chart features. I am trying to keep this sheet simple since it is an intake tracker I will be handing off. If anybody has any ideas on how I can use a formula to calculate duration that would be very helpful.

The formula I created originally worked but now it says "Invalid Data Type". The first formula is:

=IF([Request Date]@row = [Manager Approval Date]@row, 0, NETWORKDAYS([Request Date]@row, [Manager Approval Date]@row, {Copy of Amgen Holidays Range 2}))

The second formula is

=IF([Manager Approval Date]@row = [Admin Approval Date]@row, 0, NETWORKDAYS([Manager Approval Date]@row, [Admin Approval Date]@row, {Copy of Amgen Holidays Range 2}))

2023-03-05_17-41-36.png

Nick Korna

Hi @ksabrin

The "Invalid Data Type" error is likely caused by one or more of your columns not having Date as it's type and thus not being readable by the NETWORKDAYS function.

ksabrin

@Nick Korna This doesn't make sense to me because when I originally created the formula it worked but when I went back into the sheet again it gave me the invalid data type area. I just checked all of my date columns and they all have date as their type and are also restricted to date values only.

Nick Korna

I said this as the attachment picture has a column listed as Primary column, which is the default one and is always text and can't be changed:

Looking at the dates in your picture I am guessing these are holiday dates in your cross sheet reference - is that one a date column?

ksabrin

@Nick Korna Yes the one in the cross reference sheet is also a date column. Originally, I had it as column 2 as a date column type but since I was getting reference errors and invalid data type errors I moved it to the primary column as a text/number but I just moved it back to being a date column. All of my columns now are date columns restricted to date values only.

Nick Korna

I can't see a problem with your formulas and if your date columns are sorted (as well as the cross sheet reference being on the right column) then these should be generating the correct outputs for you.

The only other way I've been able to get an invalid data type error is if one of the Request/Manager Approval Dates are blank, but I doubt this is the case and this can be fixed with an IFERROR addition to the formula.

I must admit, I'm a little stumped what would keep giving you the error. 🤔