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.

MIKE

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:

=NETWORKDAYS((A3,B3,$AA$1:$AA$5)-1)&HOUR(MOD(B3-A3,1))&MINUTE(MOD(B3-A3,1))

MIKE

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:

=(NETWORKDAYS(A3,B3,$AA$1:$AA$5)-1)&HOUR(MOD(B3-A3,1))&MINUTE(MOD(B3-A3,1))

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

Fred

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.http://www.computing.net/howtos/sho...

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.MIKE

Ask Your Question

Weekly Poll