How do you calculate the amount of time between two dates but also include an IF?

Kristin Rankin

For example, I want to calculate the amount of time between two dates but only specific to a particular column of data. In this case it is the duration for negotiating a Master Agreement with the Agreement type included in a separate column.

Hannah H

Hi @Kristin Rankin,

You can build an IF statement and include NETWORKDAYS to count the number of working/business days between the start and end date.

I have assumed that you have both a start and end date column and that your Agreement Type column might have a status of "New".

=IF([Agreement Type]@row = "New", NETWORKDAYS([Master Agreement Start Date]@row, [Master Agreement End Date]@row))

I hope this helps!

Best,

Hannah

Kristin Rankin

Thanks Hannah. I think that I am headed down the right path, but was hoping you could help with a slightly different example. Within my Sheet, I have a column for Duration that sums the Start Date to Actual Close Date. On my Report I have created a formula to average the duration so that I can report on how many days it took to negotiate an agreement. Now I would like to create a new average of the duration but split it out between the length of time it took to negotiate the Master Agreement vs the order. Within my Sheet, I have already identified in two separate columns if it is a Master or Order. So I am looking for that formula as was thinking I could use AVERAGEIF but I don't want to count any blanks. I have included some screenshots. Any help is much appreciated.

Capture duration using IF, no blanks.PNG

Report duration using IF, no blanks.PNG