Example numbers:
21130 & 21065
I want to check each number if EITHER of the two conditions is true:
1. if the third digit from the right (the hundreth place) is greater than zero;
or
2. if the second digit from the right (the tens place) is >=6.
If either is true I want to add a particular number to the original number.
My example numbers meet questions 1 & 2, respectively.
Any help on this?
Thanks again!
Hey!
Try this...
=IF(OR((MID(B14,LEN(B14)-2,1)+0>0),(MID(B14,LEN(B14)-1,1)+0>=6)),B14+"Add the Number",FALSE)
umm... i tried the formula on B14... u'd have to replace that, and "add the number" is where u'll have to add wherever the number is and change False to wotever u wanna do if the condition doesnt match...
Hope this helps
Regards
Mohit
hey thanks. i didn't see that one coming, but looks good! now if i could find a way around VBA for doing loops....
perhaps
=IF(OR(MOD(A1,100)>=60,MOD(A1,1000)>=100),A1+1000,A1)
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Cool.
On a related note, I'm using a spreadheet app on my iphone. I need to add times, but this app doesn't include the custom [h]:mm. Therefore, any hour/minute figures whose summation is greater than 24:00 roll back to "a.m." times and not the answer I'm looking for.
Example:
6:50 (6 hour, 50 min)
+
18:00 (18 hour, 0 min)
Should equal 24:50, but this app rolls it to 00:50, or nearly 1 a.m.
Any workarounds out there to this?
I can't use decimals as I need to be precise to the minute. Can time values do this? I wish the app included the format limitations in the product description.
Last edited by rcane; 11-18-2009 at 05:36 PM.
Format the result as [h]:mm or [hh]:mm
Entia non sunt multiplicanda sine necessitate
Hi there,
As I indicated in the post, I cannot use the custom [h] settings. I'm looking for low-tech ways to add times.
You can convert the result with sufficient decimal precision to be accurate to the minute. Or format as d:h:m. Or scale to integer minutes.
I'm listening. Can you put an example down?
I've always thought of decimals as being in the range of +3/-2 minutes of the 'point.' Ex: 6.6 could be 6 hours and 34-39 minutes.
I have to have it within one minute to make this work, unfortunately.
To take your prior example, (6:60 + 18:00) * 24 is 24.83 hours to two decimals. A number displayed as 24.83 could actually be between 24.825 and 24.835, corresponding to d:h:m values of 1:0:49:30 to 1:0:50:06, a 36s span.
If you need more precision, add another decimal.
I'll try that. I don't know if the iphone app will respond to it.
Sadly, if the total is 18 hours 36 minutes, it can't be 35, or 37. I need to know it's 36.
And, I can't have it roll back to 1:xx:xx. I'm adding hours, not clock hours. Duty time to be specific. So, 6:50 + 18:00 needs to be reflected as 24:50.
I'll try and report.
Last edited by rcane; 11-18-2009 at 06:37 PM.
Ok, I converted the times to decimal (3 places); then used (int) and ("x"-(int)"x") to separate the wholes from the decimal fraction. From there I just found the decimal fraction's ratio to 60 minutes and concatenated the whole number next to the ratio of 60 with a nice ":" in the middle to look like a time clock.
What a pain. Next time I ask around if the app can run true excel functions. It's got about 200 of them...just not the one i needed.
Thanks to all that helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks