Trouble with "time" in formulas

[rhollo]

I have a time sheet that looks something like this:

A1=time...A2=time...A3=A2-A1+if(A1/24,1)...A4=if(or(A3<time(0,15,0),A3>time(4,0,0)),A3,time(4,0,0))

A1 and A2 are set up as dropdown lists and the times in the list are i
increments of 15 minutes. WHat I'm trying to accomplish is (in cell A4
if A3 is between 15 minutes and 4 hours, then return 4 hours. if A3 i
0 or greater than 4 hours, return A3.

If I enter enter times in A1 around 00:00 and A2 a little after 00:0
it works fine... example:

A1=22:00...A2=01:00...A3=03:00...A4=04:00
A1=20:30...A2=00:15...A3=03:45...A4=04:00
A1=00:00...A2=00:00...A3=00:00...A4=00:00

If I enter times after 00:00 in either A1 or A2 it doesn't work..
example:

A1=01:00...A2=03:30...A3=02:30...A4=02:30
A1=13:00...A2=14:15...A3=01:15...A4=01:15

Can anyone help

 

[Frank Kabel]

Hi
try
=IF(A3=0,0,MAX(A3,4/24))
and format this cell as time

 

[rhollo]

Hey Frank,

Thanks for your reply, unfortunately your formula is giving me the sam
problem as mine... works great bridging over the 00:00 hour with A1
A2 but any other time it returns that exact time from A3 instead o
4:00 when A3 is less than 4:00

 

[Frank Kabel]

Hi
does A3 really contain a Excel time value?. because MAX(A3,4/24) should
ensure that you get at least 4 hours

 

[rhollo]

Yes, A3=A2-A1+if(A1/24,1)... and A3 is formatted for time...h:mm. I hav
been doing some trouble shooting and I think the problem is in th
formula I have in A3, particularly with
the "A1/24,1" portion of it

 

[Frank Kabel]

Hi
yes this seems to be the problem. what do you want to calculate with
this expression?

 

[rhollo]

Hey Frank,

Thanks for all your help. I finally figured it out. The porblem wa
that my formula in A3=A2-A1+if(A1/24,1) when changed to a time forma
of [h]:mm, showed up as the result of A2-A1 + 24 hours. So I went wit
this formula: A3=IF(A3>B3,(B3-A3+IF(A3/24,1)),B3-A3) and it work
great.

Thanks again for your hel