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

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
    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!



  • 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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!