PDA

View Full Version : Solved: Adding Time with 30 minutes interval



jammer6_9
07-27-2009, 01:10 PM
Formula:

=IF(C7=A7,"TIME MATCH",C7+TIME(0,30,0))

Above formula works very fine but when it reaches 23:00, i can no longer found a match up with a7 and c7?

I s it maybe because date is already changing? Is there anyway to add 30 minutes to a certain time without changing the date?

Bob Phillips
07-27-2009, 01:20 PM
Is this what you mean

=IF(C7=A7,"TIME MATCH",MOD(C7+TIME(0,30,0),1))

jammer6_9
07-27-2009, 01:35 PM
So quick response xld thanks but same result is coming out. When I it reaches 22:00 + 30 minutes which is 22:30, there is no more match.

Please see attached file.


Is this what you mean

=IF(C7=A7,"TIME MATCH",MOD(C7+TIME(0,30,0),1))

Bob Phillips
07-27-2009, 01:55 PM
Okay, here is another shot

=IF(ROUND(C7*48,0)=ROUND($A$7*48,0),"TIME MATCH",C7+TIME(0,30,0))

jammer6_9
07-27-2009, 01:59 PM
:beerchug: at 23:00 and 23:30 but as it goes until 00:00 and later again no more match


Okay, here is another shot

=IF(ROUND(C7*48,0)=ROUND($A$7*48,0),"TIME MATCH",C7+TIME(0,30,0))

Bob Phillips
07-27-2009, 02:08 PM
ANother one,

=IF(B8="TIME MATCH",TIME(0,30,0),IF(ROUND(C7*48,0)=ROUND($A$7*48,0),"TIME MATCH",C7+TIME(0,30,0)))

jammer6_9
07-27-2009, 02:15 PM
:bug: Same result

Bob Phillips
07-27-2009, 02:53 PM
Then I don't understand. Can you explain what happens in which row, and what you want to happen?

jammer6_9
07-27-2009, 10:47 PM
Cell "A7" is changeable
Cell "B7" is changeable (Ex : 8:00)

Cell "C7" Formula:
=B7+TIME(0,30,0)

Cell "C8" to "C54" formula:
=IF(C7="TIME MATCH",TIME(0,30,0),IF(ROUND(C7*48,0)=ROUND($A$7*48,0),"TIME MATCH",C7+TIME(0,30,0)))

On the formula you gave, time matching is upto 23:30 only but when it reaches 00:00 and later it does'nt.

Sorry if I don't explain it well:motz2: Again on my attachement, if you will change the value of cell a7 to 00:00, 1:00, 2:00, 3:00, 4:00, there is no match up anymore. Maybe I guess it is because the day or date is different already.

Bob Phillips
07-28-2009, 01:50 AM
Not sure I fully understand yet, but I will plug away

=IF(B8="TIME MATCH",$A$32+TIME(1,0,0),IF(ROUND(MOD(C7,1)*48,0)=ROUND($A$32*48,0),"TIME MATCH",MOD(C7,1)+TIME(0,30,0)))

jammer6_9
07-28-2009, 09:39 AM
As I play around with your formula I come up to this which give I what I wanted. Thank you so much xld... :thumb

=IF($A$7="TIME MATCH",$A$7+TIME(1,0,0),IF(ROUND(MOD(C16,1)*48,0)=ROUND($A$7*48,0),"TIME MATCH",MOD(C16,1)+TIME(0,30,0)))



Not sure I fully understand yet, but I will plug away

=IF(B8="TIME MATCH",$A$32+TIME(1,0,0),IF(ROUND(MOD(C7,1)*48,0)=ROUND($A$32*48,0),"TIME MATCH",MOD(C7,1)+TIME(0,30,0)))

Bob Phillips
07-28-2009, 10:13 AM
Sorry, I moved a cell down the sheet to be able to see what was happening, and forgot to re-instate it before posting the answer.

jammer6_9
07-28-2009, 12:54 PM
:bow: I should be the one to be sorry xld for not explaining well. It is your formula that gives what I want to happen in my workbook. :) Thank you



Sorry, I moved a cell down the sheet to be able to see what was happening, and forgot to re-instate it before posting the answer.