Could you share the field name specifically and how the format changes? I would like to reproduce the behavior so as much information as possible is helpful. One thought I had is that you mention that you inherited this dashboard. You might try setting up a new dashboard on GA that mimics what was already built and see how that goes in comparison. I have found that a few of my older dashboards on GA data had to be rebuilt due to big changes on the GA side. After building a fresh data connection and using the fields available, the reports ran faster and have been fine ever since. I do not use the time on site measure though so I am not specifically familiar with changes to that one.
This is a dashboard I built. The dashboards with "Avg Time on Site" were built by a predecessor. Maybe that is why she used [Avg Time on Site].
The field name I use is [Total time on site (h:m)]. It is a date time data type, just like [Avg Time on Site], but it requires different handling.
Step 1: Convert [Total time on site (h:m)] to a string field.
Step 2: Using trim and split functions, separate out the hours, minutes, and seconds.
Step 3: Convert everything to seconds and add the three together.
Step 4: Calculate the average seconds. Format to h:mm:ss.
The first time, I had a date time field like this one: 1899/12/31 12:00:00 AM
The second time, I had a date time field like this one: 1899-12-31 12:00:00AM
The third time, I had a date time field like this one: 12:00:00 AM
The data pull is using the same tool each time (SuperMetrics Data Grabber).
The data is stored in Access (storing it in Excel or SQL Server made no difference).
I'm tempted to pull all the data again using [Avg Time on Site] since that might be an easy way out. But I can't just let this go and do the simple thing yet. ;-) I want to know why it's happening.