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

Do you think Samsung's Bixby will compete well against other phone AI systems?

Discuss in The Lounge

Poll History