Wildcards in a formula with a colon

[XrayLemi]

Okay,
I have a formula that deals with time. One cell (C4) has a set value it is a whole number. The entries in column I can be either numbers like 55, 6.5, 4.556. It can also be time like 33:30, 4:03 00:06.5. column J gives 3 options Min., Sec., or Min./sec. depending on the entries in I and J the return value is either a one or zero. I use the absolute values so it won't matter if it is time with a colon or a number value. It is as follows.

=IF(AND((ABS(I6))>=(ABS($C$4)),J6="Min."),1,IF(AND((ABS(I6))>=(ABS($C$4))*60,J6="sec."),1,IF(AND((ABS(I6))>=(ABS(($C$4)&":00")),J6="Min./Sec."),1,0)))

This works, unless the user fails to enter zeros before the colon in a time entry. Instead of entering 00:06.5 they enter :06.5. This messes things up because the ABS function has no idea what to do with it.

I want to add a section to the formula something like this. =IF(I6=" :*",(ABS("00"&(I6))),0) So if a user fails to enter the zeros, the formula will do it for them. The problem is with the wildcard. I don't think it likes the colon. Is there a way to do this?
Thank you in advance,
Jim

 

[sparky2205]

=IF(LEFT(I6,1)=":",(ABS("00"&(I6))),0)

 

[XrayLemi]

Hi Sparky,
If I use what you gave me together with an AND statement alone like this.....
=IF(AND(LEFT(I6,1)=":",ABS("00"&(I6))>=ABS((C4)&":00"),J6="Min./Sec."),1,0) Everything works fine.
If I use the rest of my formula alone,
=IF(AND((ABS(I6))>=(ABS($C$4)),J6="Min."),1,IF(AND((ABS(I6))>=(ABS($C$4))*60,J6="sec."),1,IF(AND((ABS(I6))>=(ABS(($C$4)&":00")),J6="Min./Sec."),1,0))) This works fine as well.
When I combine the two,
=IF(AND((ABS(I9))>=(ABS($C$4)),J9="Min."),1,IF(AND((ABS(I9))>=(ABS($C$4))*60,J9="sec."),1,IF(AND((ABS(I9))>=(ABS(($C$4)&":00")),J9="Min./Sec."),1,IF(AND(LEFT(I9,1)=":",ABS("00"&(I9))>=ABS((C4)&":00"),J9="Min./Sec."),1,0))))
OR in a different order,
=IF(AND(LEFT(I9,1)=":",ABS("00"&(I9))>=ABS((C4)&":00"),J9="Min./Sec."),1,IF(AND((ABS(I9))>=(ABS($C$4)),J9="Min."),1,IF(AND((ABS(I9))>=(ABS($C$4))*60,J9="sec."),1,IF(AND((ABS(I9))>=(ABS(($C$4)&":00")),J9="Min./Sec."),1,0))))
It returns a #VALUE! error.
What am I missing?
Thanks,
Jim

 

[sparky2205]

It's difficult for me to test this completely on my side as I don't have your data and it seems to be very variable.
But in your formula:
=IF(AND((ABS(I9))>=(ABS($C$4)),J9="Min."),1,IF(AND((ABS(I9))>=(ABS($C$4))*60,J9="sec."),1,IF(AND((ABS(I9))>=(ABS(($C$4)&":00")),J9="Min./Sec."),1,IF(AND(LEFT(I9,1)=":",ABS("00"&(I9))>=ABS((C4)&":00"),J9="Min./Sec."),1,0))))
IF(AND(LEFT(I9,1)=":",ABS("00"&(I9))>=ABS((C4)&":00"),J9="Min./Sec.") is only testing where the I9 has no "00" at the start. It doesn't test where I9 already starts with 00.
Try:
=IF(AND((ABS(I6))>=(ABS($C$4)),J6="Min."),1,IF(AND((ABS(I6))>=(ABS($C$4))*60,J6="sec."),1,IF(AND((ABS(I6))>=(ABS(($C$4)&":00")),J6="Min./Sec."),1,IF(LEFT(I6,1)=":",IF(AND((ABS("00"&I6))>=(ABS(($C$4)&":00")),J6="Min./Sec."),1,),0))))

 

[XrayLemi]

Hi Sparky,
That formula works with all the different variables except when the time is entered like :45 Then it returns the #VALUE! error.
Before entries like 00:06 or :06 gave the error

 

[sparky2205]

Is it possible to upload some sample data?
You could use a file share site like tinyupload.com

 

[XrayLemi]

I wish I could, too many firewalls and regulations. those sites are blocked. I can't even send it to my home e-mail. That's blocked as well. I'm even surprised I can access Mr. Excel. I would re-create the file from home, but I only have Excel 2007.

 

[sparky2205]

Right, let's have another go then. Try this:
=IF(LEFT(I6,1)=":",IF(AND((ABS("00"&I6))>=(ABS($C$4)),J6="Min."),1,IF(AND((ABS("00"&I6))>=(ABS($C$4))*60,J6="sec."),1,IF(AND((ABS("00"&I6))>=(ABS(($C$4)&":00")),J6="Min./Sec."),1,0))),IF(AND((ABS(I6))>=(ABS($C$4)),J6="Min."),1,IF(AND((ABS(I6))>=(ABS($C$4))*60,J6="sec."),1,IF(AND((ABS(I6))>=(ABS(($C$4)&":00")),J6="Min./Sec."),1,IF(AND((ABS("00"&I6))>=(ABS(($C$4)&":00")),J6="Min./Sec."),1,0)))))

 

[sparky2205]

I did a bit more testing and it falls over on: =ABS($C$4)&":00")
The purpose of the ABS function is to return the number without its sign. i.e. ABS(-1)=1. ABS(2)=2 but ABS(2&":00")=0.083333. So, you won't get the correct result.
What is the purpose of adding ":00" to the end of the value in C4 when the value of J6 is Min./Sec.?
In the following formula I removed that extra ":00" and also tidied up the formula a bit.
=IF(LEFT(I6,1)=":",IF(AND(ABS("00"&I6)>=ABS($C$4),J6="Min."),1,IF(AND(ABS("00"&I6)>=ABS($C$4)*60,J6="sec."),1,IF(AND(ABS("00"&I6)>=ABS($C$4),J6="Min./Sec."),1,0))),IF(AND(ABS(I6)>=ABS($C$4),J6="Min."),1,IF(AND(ABS(I6)>=ABS($C$4)*60,J6="sec."),1,IF(AND(ABS(I6)>=ABS($C$4),J6="Min./Sec."),1,0))))

 

[XrayLemi]

Because sometimes I6 is a number for example 45 sec. or 5 min. or .6 sec. other times it is in minutes and seconds. like 30:05 Min./
Sec. or 00:05 Min./Sec. It all depends on where or what machine the read out comes from. some units have multiple read outs. like 30:15 min. /sec. or 1815 sec. So I need the time entries to be flexible. Where I have the biggest problem is people omitting the zeros in front of the colon. for example, :45 min./sec. instead of 00:45 min./sec. If everyone put the zeros in I would not have an issue. I also cannot chase people to fix their entries. I also can't make people understand that there is a difference between 10:30 min./sec. and 10.3 min. so when J6 sees a colon, the only choice from a pull down menu is Min./Sec. if there is no colon, the choices are min. or sec.