Solved How can this function be modified to do stats?

October 26, 2015 at 09:14:14
Specs: Windows 8
Hi - I would like to do time statics for a set of data in a table so I can calculation mean, mode, etc. How can I easily strip the characters and still perform calculations?

How about this then:
=NETWORKDAYS((A3,B3,$AA$1:$AA$5)-1)&" Days "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes"

Simply subtract 1 from the =NETWORKDAYS() function.

It will then show 0 Days XX Hours XX Minutes.


See More: How can this function be modified to do stats?

Report •

October 26, 2015 at 11:34:03
Your going to have to explain, in a lot more detail, what it is your trying to do.

Showing a formula, without context, does little to explain your problem.

If all you want is the formula stripped of text then try this:



Report •

October 26, 2015 at 13:21:34
Mike - I have attempting to perform data collection where I track interval time between activities. I could not identify an Excel function using a time format that includes days and hours (I don't need accuracy to the minute) where I can determine means, modes, standard deviation, etc. for the data so I can compare current to future state. I am not a programmer but just want to use the time data for process improvement data points.

Your formula does remove the characters but I don't know how I would use the result as with the mixed units (days and hours) even if I removed the minutes.

Can you suggest a method to apply statistics functions using a time format?

Note: For the formula to work, the first parenthesis has to be move to the left of Network Days:


Any advice would be appreciated as I will need to about a 1000 records with about 7 time intervals between tasks. Thank you.


Report •

October 26, 2015 at 13:43:22
✔ Best Answer
First, what does your data look like?
Are the Dates & Times in one cell, or are they separated into two cells?

Please post a short example of you data, AFTER reading this HOW-TO
which explains the use of the < PRE > tags for posting data.

From you post, do I understand correctly, that you need only Days & Hours?

What functions do you wish to preform on the data besides means, modes, standard deviation?

Also, be aware that Excel stores Dates and Times as
a number representing the number of days since January 0, 1900
plus a fractional portion of a 24 hour day: ddddd.tttttt.
This may impact the result of your analysis.


Report •
Related Solutions

Ask Question