i have some raw data that I'm trying to calculate avg time from stage to stage however running into issues with #VALUE!
Column A has date in format 01/01/2000
Column B has time in format hh:mm:ss
Column C had a formula which combines the two above columns (A and B) into 1 column. =TEXT(A2,"mm/dd/yyyy")&" " &TEXT(B2,"hh:mm:ss")
Column D has date in format 01/01/2000
Column E has time in format hh:mm:ss
Column F has a formula which combines the two above columns (D and F) into 1 column. =TEXT(E2,"mm/dd/yyyy")&" " &TEXT(F2,"hh:mm:ss")
now here's where the problem comes in for me.
Column G is where I need to determine the amount the amount of time difference between column C and column F. A couple of caveats first. Sometimes Column A/B/C will not have a time/date in them but D/E/F will. when this happens, a #value! is displayed.
so through about 500 rows I need to determine the avg of time for column G. Can't get it to work. Always generates a #value!
So I'm either doing something wrong, or it's not possible to do with where dates are not all input.
I do have it set in a pivot table
I have tried filtering
I have tried conditional formatting
all to no avail.