Expert: Richard Roberts - 11/4/2009

Question
Hi Richard,

Here's the expected result:

IF F1="" and H1="" then C1=do nothing
IF F1=something and H1="" then C1="Check Your Data"
IF F1="" and H1=something then C1="Check Your Data"
IF F1=something and H1=something then C1=F1-H1
IF F1 or H1 is greater than 24:00 then C1="Check your Data"

Here is my formula:

=IF(AND(F1="",H1=""),"",IF(H1="","Check Your Data",IF(OR(F1>1,H1>1),"Check Your Data",IF(F1="","Check Your Data",(H1<F1)-(F1-H1)))))

I use a 24 hour format. So my problem is for example
when F1=12:30 and H1=1:00 C1=12:30 which is wrong
C1 is supposed to =0:30

How do i do this..

Thanks.  

Answer
Polo

Very often the problem with time is the formatting.
If I copy your formula to a spreadsheet and format the cells F1 and H1 as 24 hour format, I.E. toolbar/format/cells/time select type of 13:30, and also format the cell C1 the same. Then your formula works as you want it to.

Hope this helps

Richard
Florida
USA