Wildcards in a formula with a colon

XrayLemi

Board Regular
Joined
Aug 1, 2018
Messages
76
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

sparky2205

Active Member
Joined
Feb 6, 2013
Messages
294
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
76
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
294
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
76
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
294
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
76
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
294
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
294
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
76
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,170
Messages
5,576,518
Members
412,730
Latest member
Thundereagle
Top