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]

Yes, I understand you are trying to cater for many variants in terms of data entry in I6 but why are you adding ":00" to C4 i.e. IF(AND((ABS(I6))>=(ABS(($C$4)&":00")),J6="Min./Sec."),1,0))
In your original post you state that C4 is a set whole number.
Form the testing I have done I can allow for the variants in I6. It's the addition of the ":00" to C4 that causes the issue as the ABS function doesn't deal with this correctly as described in post #9.

 

[XrayLemi]

When the user enters time, and the time value exceeds the value in C4, (usually 100 minutes) The conditional formatting changes The cell to give the user a warning. I need to add the ($C$4)&":00" to the formula to change the whole number to time. because the absolute value of 100 minutes (100:00) is 4.166667 far below the absolute value of C4 (100). Without the :00 even if the user enters 200:00 (double the allotted time) the absolute value is only 8.334 and the warning will not appear.

 

[jasonb75]

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.

Doing so will be the only way to ensure accuracy in your results, it takes more to correct mistakes than to do something correct in the first place.

Try data validation with a min time of 0 and a max time of =$C$4

100 minutes has a decimal value of 0.069444 so the validation should reject most of the invalid entries, simply use the validation warning messages to advise the user that they must enter time as h:mm:ss

You can not easily identify if there is a colon in a valid time, the colon is displayed by formatting but the value is actually a decimal value.
Additionally, a valid time entry of 10:30 is 10 hours 30 minutes, not 10 minutes 30 seconds. Something to remember if you need to do further analysis on the time data at a later point.

 

[XrayLemi]

Okay I have a fix. It is not elegant but it now works for all scenarios.
I added another column it is column P. The formula in this column is =IF(LEFT(I6,1)=":","00"&I6,I6)
The formula I used to figure the time is based on column P not column I. Column I is displayed, Columns O and P are hidden and locked. =IF(AND(ABS((P6))>=(ABS(($C$4)&":00")),J6="Min:Sec"),1,IF(AND((ABS(P6))>=(ABS($C$4)),J6="Min."),1,IF(AND((ABS(P6))>=(ABS($C$4))*60,J6="sec."),1,0)))

Jason, the time format I use for Min:Sec comes right from the equipment we use. It is not my choice how it is formatted. I did however change the way minutes and seconds are in the spreadsheet. it is now displayed Min:Sec to more correspond with the readouts on the equipment instead of Min./Sec. As for the data validation, I have an input message that appears as soon as the cell is selected. It shows how time should be entered. It is however ignored.

 

[jasonb75]

From your earlier posts I had assumed that the times were being typed in, if they are imported to the sheet by macro or entered by copy and paste then validation will ignore the entry.

You say that your fix is not elegant, but with what you have been given to work with there is not a lot that can be done.

 

[XrayLemi]

I wish the data from the machines was imported in there would be far less errors. The data is manually entered. However, I cannot go back and check 20 or 30 spreadsheets to make sure that the entries are correct. The other problem I have is data validation, when it comes to time will only allow up to 59:59 in this format. The place where users get the time values from will only display time as 00:00 so if I have a user who gets a value of 100:00 (100 minutes, I can assure you they will not break it down to 1:40:00. This will also not account for the machines that only display time in decimal minutes. I have also tried multiple time columns. One with entries like 59:59, the other with decimal entries. this resulted in users putting time in where ever they felt like it. The option I ended up with is my best option.

Thank you guys for all the help and input. Without you I'd be sunk.
Jim