# worksheet function – How to calculate average number of days, hours and minutes in excel

Can anyone help me please to calculate average number of days, hours and minutes in excel? It is to calculate the average time taken to complete maintenance jobs.

To start I merged the date and time cells for both start of the job and the end of the job. Then for each job I calculated how long it took. I have tried with 3 different formulae each giving the same answer (formulae below). The format of each answer is days:hours:mins so for exampe 28:06:35

Now I need to calculate the average of that whole column. The formula needed must also be able to us an iferror function as some of the jobs are not closed and therefore the ‘how long it took’ column returns a #value! error so need to factor this in.

version 1 – =INT(N265-C265)&TEXT(N265-C265,”:hh:mm”)
Version 2 – =O265-INT(O265)
Version 3 – =INT(N265-C265)&”:”&HOUR(MOD(N265-C265,1))&”:”&MINUTE(MOD(N265-C265,1))

The reason I’ve created 3 columns each with a different formula is to see if any of the formulae make finding the average easier but I’ve searched the web and not found a solution.