1 Reply Latest reply on Sep 1, 2018 7:08 AM by Deepak Rai

    Average number of days between two dates, broken down by month?

    Russ Tanner

      I work with a school. The spreadsheet we have has several columns, including three date columns - Inquiry Date, Tour Date, Enroll Date. There is also an Admissions Director column (we have one admissions director for each of the 4 campuses). The spreadsheet I'm working with has several hundred rows.

       

      There are a few things we're trying to calculate.  I'm stuck on a few things. I need to figure out the average number of days, by month, and by admissions director that it takes for a lead to go from Inquiry Date to Tour Date. We need the same average number of days for Tour Date to Enroll Date (again, by month and by admissions director). Ideally, I'd like to have a bar chart with a column for each month and then the each month has four bars that show the average number of days for each admissions director.

       

      I created a calculated field using Inquiry Date and Tour Date - (DATEDIFF('day',[Inquiry Date] ,[Tour Complete Date])) The problem is when I drag that calculated field to the worksheet it calculates the number of days between the dates for each row and then adds all of those days together. That doesn't really help. I need the average, not the sum.

       

      Any thoughts on how to accomplish? Thanks!