SOLVED

Help with constructing avg. waitlist times using datedif function

Copper Contributor

I'm trying to calculate the average wait time for different types of apartments at my job. For example, I want to be able to differentiate the wait times between a one bedroom with a mini kitchen versus a one bedroom with a full kitchen. I'd like the second table (avg. length of time july 2021) to show these results. I can't find any information with exactly what I'm looking for online.

 

The current formula I am using to calculate the wait times is :

 

=DATEDIF(D2,E2,"y") &" years,"&DATEDIF(D2,E2,"ym") &" months," &DATEDIF(D2,E2,"md") &" days" 

 

If anyone could help me that would be great!

5 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@MeganM1130 

The DATEDIF formula results in a text string; that is useful for display purposes but you cannot calculate an average of text values.

Add a column with the simple formula =E2-D2. Format the cell with the formula as General and fill it down. It shows the wait time in days.

You can then create a pivot table based on the data.

Add the apartment type to the Rows area and the new column to the Values area. By default, Excel will sum the wait times, but you can change that to average in the Value Field Options dialog.

@MeganM1130 

I'd simply subtract one day from another receiving wait time in days. With that you may do some arithmetic for stats - average, percentage, etc. With texts return by formula you tried to use you can do nothing of that.

@Hans Vogelaar @Sergei Baklan 

 

Thank you both for your help - this is exactly what I needed to know :)

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@MeganM1130 

The DATEDIF formula results in a text string; that is useful for display purposes but you cannot calculate an average of text values.

Add a column with the simple formula =E2-D2. Format the cell with the formula as General and fill it down. It shows the wait time in days.

You can then create a pivot table based on the data.

Add the apartment type to the Rows area and the new column to the Values area. By default, Excel will sum the wait times, but you can change that to average in the Value Field Options dialog.

View solution in original post