Excel 2016 Time Avg Issue

i have some raw data that I'm trying to calculate avg time from stage to stage however running into issues with #VALUE!

Stage 1
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")

Stage 2
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.
Todd WSystems AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
There are two issues with the formula.
1) When there is no date in column A, column C is still constructing the timestamp from column A and B.
2) The resultant value is a Text, not a number where point to remember is excel treats date and time as real numbers. So your average formula will also not work with existing formula.

Please give this a try...


Open in new window

Custom Format the formula cell as [h]:mm and then copy it down.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yo_beeDirector of Information TechnologyCommented:
Here is another way.
Formula I used  

Open in new window

yo_beeDirector of Information TechnologyCommented:
Glad to help.
Eric ZwiekhorstSAP Business ConsultantCommented:
In the column where you calcuate the difference you need to add a if statement to ensure the fileds are populated.
If you do this you will not get the #value and the average will work.
If you do not know how to do this I will help , just ask

Kind regards
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.