Forum Discussion
Help with constructing avg. waitlist times using datedif function
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!
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.
5 Replies
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.
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.
Sorry, missed your post
No problem!
