Time Calculation

**faisal.ta** · 01-03-2011, 04:28 PM

Hi
In an Excel sheet from Q8:Q20 i have entered many timings ( like 9:15 AM, 11:45 AM, 3:00 PM, 5:00 PM, 7:15 PM, etc ) in many cells. Now can we have a formula where it calculates the minimum Time and the maximum time and then show the total minutes between between them.
Like from the above example the minumum Time was 9:15 AM and Max was 7:15 PM and it should show how many minutes was there between two timings.

Faisal

**TechRetard** · 01-03-2011, 04:49 PM

This should work...

=Max(Q8:Q20)

to determine the max

=min(Q8:Q20)

to determine the min
and then

=60*(Max(Q8:Q20)-min(Q8:Q20))

to determine the # of minutes between...note you will need to change the cell format to [h]:mm

**ChemistB** · 01-03-2011, 04:50 PM

=MIN(Q8:Q20)
=MAX(Q8:Q20)
=MAX(Q8:Q20)-MIN(Q8:Q20)
Format cells as [mm]:ss
Does that work for you?

**faisal.ta** · 01-03-2011, 05:05 PM

Ya it worked, but there is a small hiccup.
The Time break normally is from 12 AM to 11:59 PM. It calculates min and max in between this. But what i want is the Minimum Time should be 12 PM and Maximum Time will be 11:59 AM.
Most of our production happens between 4 PM and 5 AM in the morning. SO i want to calculate the Production hours between these time. So with the above formula if we give then the minimum time is the time entered after 12:00 Am. and this is not solving my problem.