Forcing numbers to be 2 digits when adding to the middle of a string.

[phkc070408]

Hi all: I posted this as a continuation on a different thread, but it was recommended that I repost it as a new thread:

I work in a transportation field and am responsible for rail schedules. I am trying to add a fixed number of minutes to the row above. The problem is that the display formats of time that are available (from the format menu) don't match the industry standard (6:21A, 10:14P). Also, we sometimes add text in front of the time which have different meanings to our crews (S 6:21A, L 10:14P). Due to these anomalies, I have discovered that it is easier to keep things in "general" or "text" format.

Unfortunately this leads to a few complications when adding a fixed number of minutes to the cell above. So far, I have the following formula. For Cell D122, I use the following: =IF(LEN(D121)=5,LEFT(D121,2)&(MID(D121,3,2)+3)&(RIGHT(D121,1)),LEFT(D121,3)&(MID(D121,4,2)+3)&(RIGHT(D121,1))). The reason for the if statement is whether the hours is a one digit number or a 2 digit number.

this formula works great, except for when the minutes (positions 3/4 or 4/5) are between 00 and 07, because when I add the 2 minutes to these numbers, I end up with a 1-digit number between 2 and 9.

For example, adding 2 to the 3rd and 4th characters of 6:01A turns unto 6:3A, when're I want 6:03A.

Is there any way to force the results to be a 2 digit number?

One solution is to add a hidden row in-between with the results of the addition, force it into a 2-digit number using the formatter, and then joining the pieces together, but I was hoping to just add a string into the middle of my formula.

[AliGW]

We have no idea what the source data looks like, so the formulae you have tried are meaningless.

=TEXT(A1,"00")

would force the value in A1 to appear as two digits, so 9 would appear as 09.

Hope this helps.

[phkc070408]

The source Data would be say "S 9:05A". When I use the formula to try and add 3 minutes to it, I get "S 9:8A". My goal is is to force the "8" to be an "08". I don't want to manually add a "0" into it, because I don't want "S 9:09" to convert to "S 9:011A"

[AliGW]

I wasn't suggesting you add 0 manually. Try this:

=IF(LEN(D121)=5,LEFT(D121,2)&(MID(D121,3,2)+3)&(RIGHT(D121,1)),LEFT(D121,3)&(TEXT(MID(D121,4,2)+3),"00")&(RIGHT(D121,1)))

If this doesn't work, provide a sample workbook.

[phkc070408]

Ok, I can't post any links yet, since I haven't made enough posts. Give me a day or 2 and I'll try and respond to some other threads.

[jason.b75]

You can add a sample file to your first post as soon as you join.

Click the 'Go Advanced' button in the bottom right corner, then scroll down and look for the 'Manage Attachments' link (text link, not paperclip icon) and follow the prompts.

[Richard Buttrey]

Maybe...

Please Login or Register  to view this content.