Solved: time1 < time2 + 30?

[IkEcht]

Hi,

in my workbook I have sixteen pairs of times (hh:mm:ss).

For each pair I need to determine if the first time is smaller then the second time + 30 seconds.
Being quite unfamiliar with the excel time-functions and with the few things I tried not working (if(e4<(f4+time(0;0;30));1;0) for instance), I'm actually just taking numbers out of the cell as if it was a string and do the calculation in vba.

Now there should be better/more simple ways to do this. Can anyone give me a lead? Can be either within vba or just excel, I don't mind.

thanks!

[Bob Phillips]

Your formula looks okay. What do you want to do with the results?

[IkEcht]

My formula looks okay, but doesn't do the trick. I'll attach a workbook with the formula in place, you will see all differences give a zero as result, though there should be some ones.

I just want to count the amount of times the first time is smaller then the second time + 30 seconds. This result will be processed further on, but that is of no interest for this threat.

[Bob Phillips]

That is because all of the cells are text not numbers.

Select them all, then hit F2 then Return and you will see it all switch.

[IkEcht]

Thanks! this works.

But it raises another question, why did this happen. The numbers are all copied directly from another excel workbook where they are formated as text I guess (special: mm:ss). I don't want to change a thing in the workbook the data comes from. Is there a (vba) pastespecial or anything alike that will still do this trick of changing from text to numbers for me at once?

If not I guess I will have to refrain to text-string analysis with right() and mid() statements in vba to do the calcs, but there should be a more elegant way I guess.

[Bob Phillips]

With ActiveSheet.Columns("A:B")
.Value = .Value
End With

[IkEcht]

Thanks!