Wildcards in a formula with a colon

XrayLemi

Board Regular
Joined
Aug 1, 2018
Messages
125
Office Version
  1. 365
Platform
  1. Windows
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
 

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
Joined
Feb 6, 2013
Messages
375
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
=IF(LEFT(I6,1)=":",(ABS("00"&(I6))),0)
 

XrayLemi

Board Regular
Joined
Aug 1, 2018
Messages
125
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 6, 2013
Messages
375
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Board Regular
Joined
Aug 1, 2018
Messages
125
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Feb 6, 2013
Messages
375
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Is it possible to upload some sample data?
You could use a file share site like tinyupload.com
 

XrayLemi

Board Regular
Joined
Aug 1, 2018
Messages
125
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Feb 6, 2013
Messages
375
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Feb 6, 2013
Messages
375
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Aug 1, 2018
Messages
125
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top