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.
Bookmarks