A1 = 18:00 and B1 = 05:00

=MOD(B1-A1,1)

or

=B1-A1+(A1=B1)



--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"sammy" wrote in message
...
Thanks it works great, now I have another question how do I caculate the
hour difference between two cells in cell 1 I have 18:00 and in cell 2 I
have
05:00. any idea.

Thanks for your help.

"Peo Sjoblom" wrote:

You can use allowcustom and a formula, if we assume that A1 is the cell
you
want to validate and the time between is 21:00 and 06:00

=OR(A1=--"21:00",A1<=--"06:00")

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"sammy" wrote in message
...
Hi hope somebody can help me.

I need to validated the time on a cell, I have 3 shifts one working
from
6:00 to 15:30 other from 15:30 to 01:00 and finaly one from 18:00 to
06:00
,
I allready validated the time on shift 1 using the bettwen time option
but
I
can't make this work on the other two, (because the end date it's from
the
next day).

Any ideas ? thanks for your help.