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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.