Modifying an existing time formula

[spittingfire]

Hi All,

I have the following formula that I need adjusting and not sure how to do it.

=IF(TIME(8,0,0)-A1=30/1440,TIME(8,0,0),A1)

If I enter 7:30 or 8:00 in cell A1 a value of 8:00 is returned. What I want to do now is also add 8:30. Which means when I enter 7:30, 8:00 or 8:30 the value of 8:00 should be returned. Can one of you assist me with that?

Thanks in advance

[GTO]

Try:

=IF(TIME(8,0,0)-A1=30/1440,TIME(8,0,0),IF(A1-(30/1440)=TIME(8,0,0),TIME(8,0,0), A1))

Hope that helps,

Mark

[spittingfire]

Thanks GTO, that works well now I want to add one more to that.

In addition I will like to modify it even further to add if I also type in 9:30, 10:00 or 10:30 it will return 10:00.

Are you able to assist me with that?

Again thanks in advance for all your help GTO

[Bob Phillips]

Create a table with the input times and the revised times in say M1:N20, and use

=IFERROR(INDEX(N1:N20,MATCH(A1,$M$1:$M$20,0)),A1)

Any additions just add to the table then.

[spittingfire]

Create a table with the input times and the revised times in say M1:N20, and use

=IFERROR(INDEX(N1:N20,MATCH(A1,$M$1:$M$20,0)),A1)

Any additions just add to the table then.

Thanks for your replies, however I am still having some issues and I have attached a sample data sheet with the rows high lighted in green is what the current formula is doing and the rows high lighted in yellow is the results I will like to see. Pretty much everything on the sheet is working well except for the high lighted issues.

More than likely the formulas in M and N will need to be modified to accomplish what I need. If you need to modify anything else or added any new formulas to achieve the end results that I am looking for please feel free to do so.

[Bob Phillips]

Try the lookup table I suggested, it is much simpler.

[spittingfire]

Thanks xld - I have tried what you suggested and in a blank sheet it works as expected however it doesn't produce the same results when I integrate it into the sheet. I am sure I am doing something wrong but can't get it right now matter how much I switch it around.

[Bob Phillips]

Post the workbook with the table that isn't working.

[spittingfire]

Thanks xld - I have tried what you suggested and in a blank sheet it works as expected however it doesn't produce the same results when I integrate it into the sheet. I am sure I am doing something wrong but can't get it right now matter how much I switch it around.

[Bob Phillips]

Post the workbook with the table that isn't working.

[spittingfire]

Thanks for your help xld. After many cracks at it I finally figured out what I was doing wrong. :)