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.
Book1.xlsx
LVL 2
Todd WedlakeSystems AdministratorAsked:
Who is Participating?
 
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...

=IF(OR(A2="",D2=""),"",F2-C2)

Open in new window

Custom Format the formula cell as [h]:mm and then copy it down.
0
 
yo_beeDirector of Information TechnologyCommented:
Here is another way.
Formula I used  
=IF(OR(ISBLANK(A2),ISBLANK(B2),ISBLANK(D2),ISBLANK(E2)),0,(((D2+E2)-(A2+B2))*24))

Open in new window

Time.xlsx
0
 
yo_beeDirector of Information TechnologyCommented:
Glad to help.
0
 
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
Eric
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.