Hi all,

I am using to schedule arrivals/departures.

In cell J3 i am look for a formula to find the earlier time in column F, these times in column F occure both before and after midnight ie. 19:30 is earlier than 01:00 in terms of a arrivals for a night shift. I need a formula to find the earliest time in the list taking this into account, min does not work as it sees 00:30 as earlier than 21:30 for example.

Any help would be much appreciated.

Thanks
Kiktuo

Hi,

Am Mon, 24 Nov 2014 17:51:20 +0000 schrieb kiktuo:

In cell J3 i am look for a formula to find the earlier time in column F,
these times in column F occure both before and after midnight ie. 19:30
is earlier than 01:00 in terms of a arrivals for a night shift. I need a
formula to find the earliest time in the list taking this into account,
min does not work as it sees 00:30 as earlier than 21:30 for example.

is a date in another column?
Please post an example of your data and show us your expected result.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Create another column, and populate it with the time offset so that 0 is the "base" time e.g. 6pm. So if A is your time, set B1=MOD(A1+.25,1) because .25 is a quarter of a day, and 6pm is a quarter of a day before midnight. Then you can do a min on this column, and then =MOD(An-.25,1) again to find the time where n is the row of the min.

Phil.

Example:
https://dl.dropboxusercontent.com/u/.../TimeShift.xls