Jul 15 2021 12:45 PM
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!
Jul 15 2021 01:36 PM
SolutionThe 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.
Jul 15 2021 01:51 PM
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.
Jul 15 2021 01:52 PM
Sorry, missed your post
Jul 15 2021 02:21 PM
No problem!
Jul 19 2021 05:35 AM
Thank you both for your help - this is exactly what I needed to know :)
Jul 15 2021 01:36 PM
SolutionThe 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.