# Wildcards in a formula with a colon

#### XrayLemi

##### Board Regular
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?
Jim

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### sparky2205

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

#### XrayLemi

##### Board Regular
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

##### Active Member
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.
=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

##### Board Regular

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

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

#### XrayLemi

##### Board Regular

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

##### Active Member
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

##### Active Member
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

##### Board Regular
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.

Replies
4
Views
214
Replies
11
Views
650
Replies
45
Views
675
Replies
7
Views
80
Replies
3
Views
208

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,498
Messages
5,832,060
Members
430,109
Latest member
tinezi

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back