Cody T.

asked • 11/05/18# Asking the excel formula

for example, when I put 0700 in one column and the last number is "00", the second column want to be 1530, if the first column is 0800, the second column would be 1630, but if the first column I put the number is 0730, means the last 2 number is "30", I want the second column is showed 1600, if 0830 in the first column, the second would be 1700.

How can I set this Formula?

One more question, if I put some chinese in the column, the second column will be showed a standard number like 30, but if i the first column is put "off" or "off XXX", means when there is "off" this text is shown in the first column, there is nothing in the second column, is it worked to make a formula?

Thank you so much! Hope you can help.!

## 2 Answers By Expert Tutors

Cody,

An alternative way to solve this is to have the 0700, 0730, 0800, 0830 values in the cells A1-A4. The first cell of the second column (B1) should have the formula:

=IF(ISNUMBER(SEARCH(3,A1)),VALUE(A1)+870,VALUE(A2)+830)

The IF statement checks each cell if any of them contains the number "3". If it does then get cell's value and add 870 to it. If it doesn't then adds 830.

Then you copy the formula in B1 to the other cells (B2-B4) to apply the same formula.

Hope this helps.

Brandon P. answered • 06/06/19

Tutor specializing in High School Math and Engineering

Hi Cody, a way to do this is the have the 0700, 0730, 0800, 0830 values in the first column as cells A1-A4. The second cell (B1) should have the following formula: =IF(RIGHT(TRIM(A1),2)="00",A1+830,A1+870).

This IF statement states to look at cell A1, which is 0700, and if the last 2 digits have "00" at the end, get cell A1's value and add 830 to it. If it does not end in "00", get cell A1's value and add 870 to it.

Drag B1 formula down the column to apply to the next rows. Let me know if you have any more questions.

-TutorB

## Still looking for help? Get the right answer, fast.

Get a free answer to a quick problem.

Most questions answered within 4 hours.

#### OR

Choose an expert and meet online. No packages or subscriptions, pay only for the time you need.

Chris S.

What do these numbers represent? Are you trying to calculate time intervals? If so, I would format your data as time data type, and use time calculations to do so. If not, please revise your description to include what type of data this is.04/18/19