Find MAX value from a range of time intervals displayed as "days", "hrs", "mins"

**Liam5** · 05-27-2020, 11:08 AM

Hi folks,
I'm trying to display the MAX, MIN and AVERAGE of a list of time intervals calculated form a range of dates/times. The Duration must remove weekends and holidays and display as days/hrs/mins. I have partial success as I can get this part to work using Networkdays function but the MAX, MIN will only display 0 values and the AVERAGE results in a #DIV/0! error.
If I use a simple End-Start calculation for the interval then the MAX, MIN, AVERAGE will work. If I use Custom cell formatting I can get the days/hrs/mins but with only the number of days as a figure.
Any ideas? Thank you in advance.

Sampling interval1.png

Cheers,
Liam

**loginjmor** · 05-27-2020, 05:49 PM

Hi -

I think your problem is when you add text to your results (" days ", etc.) Excel treats those values as text. So you can't do MIN, MAX or AVERAGE on text. Use Column E to hold your actual numerical data from your NETWORKDAYS formula, and calculate your statistics from that. Keep Column D as is for your pretty format.

BUT, without a sample workbook to look at, I'm just guessing.

Hope this helps.

**Pauleyb** · 05-27-2020, 06:01 PM

Those functions require numbers and can't work with text. So, your equation in column D needs to be a number. Use:
={the code above to give you the days}+TIME({the code above to give you hours},{the code above to give you minutes},0)
This should show you a number like 6.493055556 for your 6 days, 11 hrs, 50 mins. Excel may try to be 'smart' and change the number format to 11:50 AM, which would not hurt anything, but it is easy to change the format back to 'General' so you see the 6.493 number. Now you can use the MAX, MIN, AVERAGE functions.

You could also use some custom formatting to display it in your days-hrs-mins format (although it would have an issue if your time exceeded 31 days).

**Liam5** · 06-03-2020, 10:23 AM

Hi PauleyB,
I've followed your suggestion but the result is not matching yours. My calculated result is 6.25416 rather that the expected result 6.493055556 for that line. Any ideas? I've tried it on other lines and they are all less than expected by manual calculation. Here is a screenshot of the formula in case I've done something stupid!

Cheers,
LiamSampling interval2.png

**arthurbr** · 06-03-2020, 11:16 AM

Post a sample sheet. See yellow banner

**Liam5** · 06-03-2020, 11:32 AM

Hi arthurBr,
I have attached a sample sheet...Sampling interval_

**arthurbr** · 06-03-2020, 11:50 AM

The MIN value can be returned with =MIN(IF(E2:E7>0,E2:E7) entered as an array formula with ctrl+ Shift +enter
The average can be calculated with the AVERAGEIF function to exclude 0 values

**Liam5** · 06-03-2020, 12:04 PM

Hi arthurbr,
Thank you for that, but the catch is the values in Column E are not calculating correctly compared to Column D.
So 6days 11hrs 50mins = 6.493055556 days, instead when I run the formula suggested by PauleyB above, my spreadsheet is calculating 6.25416667 days. The rest of the values in Column E are also less that the expected result if calculated manually.
I've checked the 1904 date in File> Options> Advanced but it does not make a difference. I've removed the "-1" from the Time portion of the formula but again it doesn't bring it up far enough to match the expected result.

Cheers,
Liam

**arthurbr** · 06-04-2020, 03:31 AM

Is the attached what you need ?

**Liam5** · 06-04-2020, 09:12 AM

Hi arthurbr,
That looks perfect, thank you so much. You made it look so easy.
Did you ever find a reason for the original miscalculation? It's not really important but just a nice to know. I'm sure it's probably just a glitch somewhere.

Vielen Danke,
Liam

**Pauleyb** · 06-04-2020, 05:17 PM

Sorry to have left the party, but I have two things to add:
1) the bug in your code was that your equation used {the code to generate days} for both the days, hours, and minutes, so your time was really 6 days, 6 hours, and 6 minutes.
2) the easy way to see this is to use Formulas Task Bar -> Formula Auditing -> Evaluate Formula. This let me step through your equation to see what it was calculating.

**arthurbr** · 06-05-2020, 02:08 AM

You're welcome

Find MAX value from a range of time intervals displayed as "days", "hrs", "mins"

LinkBack

Thread Tools

Rate This Thread

Display

Find MAX value from a range of time intervals displayed as "days", "hrs", "mins"

Re: Find MAX value from a range of time intervals displayed as "days", "hrs", "mins"

Re: Find MAX value from a range of time intervals displayed as "days", "hrs", "mins"

Re: Find MAX value from a range of time intervals displayed as "days", "hrs", "mins"

Re: Find MAX value from a range of time intervals displayed as "days", "hrs", "mins"

Re: Find MAX value from a range of time intervals displayed as "days", "hrs", "mins"

Re: Find MAX value from a range of time intervals displayed as "days", "hrs", "mins"

Re: Find MAX value from a range of time intervals displayed as "days", "hrs", "mins"

Re: Find MAX value from a range of time intervals displayed as "days", "hrs", "mins"

Re: Find MAX value from a range of time intervals displayed as "days", "hrs", "mins"

Re: Find MAX value from a range of time intervals displayed as "days", "hrs", "mins"

Re: Find MAX value from a range of time intervals displayed as "days", "hrs", "mins"

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Brief formula to convert time values to "shift1", "shift2", "shift3".

Budget assist: Entering "Weekly" "Fortnightly" "Monthly" OR "Yearly" with formulas

Using "index", "match", "min", "abs", to find the closest value in multiple criteria

[SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"

Don't know the Forumlas with problem either using "vlookup" "if" "and" or "index" "match"

[SOLVED] How to USE """"" cells count """"" change font color

HELP on "left","right","find","len","substitute" functions

Bookmarks

Bookmarks

Posting Permissions