Macro to find Max and Min time from the data

[johnygaddar]

Hi Guy

Daily At the end of day We receive sales files from our different teams, these files are received at different times due to difference in working shifts. I have collated last one month data and from this data I want to know the earliest and maximum time and date on which we received the files in last month, so that i can fix the receiving time for all regions.

I am attaching sample file for your reference.

I would appreciate if some one help me on this.

[Bernie Deitrick]

Array enter (enter using Ctrl-Shift-Enter)

=MAX(A2:A10+B2:B10)
and format the cell as date and time.

For the earliest array enter

=MIN(A2:A10+B2:B10)

[johnygaddar]

Hi Bernie

Thanks for you help and reply..

The formula which you gave me {=MIN(A2:A10+B2:B10)} is perfectly working for dates, whereas its not working for the time.

Your formula gives me the min time as 23:08 and Max time as 20:00.

If you look at my file, the earliest time received was on 02Mar14 at 19:38. Whereas the maximum time was on 25Feb14 at 03:00 (Morning).


I tried various formulas but its not working. Any help either through macros or formulas will be appreciated.

[Bernie Deitrick]

You said "Time and Date" - meaning that 10PM on the 1st is earlier than 10AM on the 2nd. If you just want the earliest and latest time separate from the date, use

=MIN(B2:B20)
=MAX(B2:B20)

And since you don't care about time on date, just use

=MIN(A2:A10)
=MAX(A2:A10)

But - and this is a big BUT - you say

"earliest time received was on 02Mar14 at 19:38..the maximum time was on 25Feb14 at 03:00"

So is the earliest time relative to a specific time of the day - like 17:00? So 17:01 would be the earliest, and 16:59 would be the latest?

If that is the case, then use these array formulas, entered with Ctrl-Shift-Enter

=MIN(IF(B2:B10<17/24,B2:B10+1,B2:B10))
=MAX(IF(B2:B10<17/24,B2:B10+1,B2:B10))

[newdoverman]

From reading the previous replies, I have tried to put all the choices in one workbook.

[johnygaddar]

Thank You very much Bernie.

This time your formula worked perfectly for me.

I really appreciate the way you have responded. You were fast and quick.