I am creating a new column for an Excel program but this column needs to use
two different formulas based on a Start time in column G. If the Start time
is before midnight then I use one fomula and if the start time is after
midnight but before 4 a.m. then I use a different formula. The formulas work
perfect but I can't seem to write the initial condition correctly so that it
knows which formula to use.

Essentially, how could I write an If Then statement that says if the scan
time in cell ( #, G ) is 24:00 and < 04:00 then

Calculation # 1

Else

Calculation # 2

=IF(HOUR(G2)<4,formula1,formula2)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Scott" wrote in message
...
I am creating a new column for an Excel program but this column needs to
use
two different formulas based on a Start time in column G. If the Start
time
is before midnight then I use one fomula and if the start time is after
midnight but before 4 a.m. then I use a different formula. The formulas
work
perfect but I can't seem to write the initial condition correctly so that
it
knows which formula to use.

Essentially, how could I write an If Then statement that says if the scan
time in cell ( #, G ) is 24:00 and < 04:00 then

Calculation # 1

Else

Calculation # 2

Bob,

I think this is the right track I need to take but I have found the formula
is even more complex and can not get it to work. Hopefully you can help. It
is going to have to be a nested If Then Statement.

The first thing I need to do is see If B2=B1
If it is false then put 0.
If it is true then need to test If Hour(G2)<4
If this is True then need this formula
ROUND(MOD(G2-H1,1)*1440,0)
else use this formula
ROUND(MOD(G2-MAX(H1,(VLOOKUP(A2,RRInfo,5,FALSE))),1)*1440,0)

Do you know how to put this together into one big formula??

"Bob Phillips" wrote:

=IF(HOUR(G2)<4,formula1,formula2)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Scott" wrote in message
...
I am creating a new column for an Excel program but this column needs to
use
two different formulas based on a Start time in column G. If the Start
time
is before midnight then I use one fomula and if the start time is after
midnight but before 4 a.m. then I use a different formula. The formulas
work
perfect but I can't seem to write the initial condition correctly so that
it
knows which formula to use.

Essentially, how could I write an If Then statement that says if the scan
time in cell ( #, G ) is 24:00 and < 04:00 then

Calculation # 1

Else

Calculation # 2

If Range("G1").Value TimeSerial(0, 0, 0) _
And Range("G1").Value < TimeSerial(4, 0, 0) Then
' Calculation # 1
Else
' Calculation # 2
End If

Regards,
Stefi

€žScott€� ezt Ã*rta:

I am creating a new column for an Excel program but this column needs to use
two different formulas based on a Start time in column G. If the Start time
is before midnight then I use one fomula and if the start time is after
midnight but before 4 a.m. then I use a different formula. The formulas work
perfect but I can't seem to write the initial condition correctly so that it
knows which formula to use.

Essentially, how could I write an If Then statement that says if the scan
time in cell ( #, G ) is 24:00 and < 04:00 then

Calculation # 1

Else

Calculation # 2

Both of these make sense but even trying both gives me errors. I think the
problem lies with the formulas I am using that already contain some nested If
Then statements. Let me post them and see if anyone knows how to integrate
this.

Here is the formula I am using if it is 24:00 and <04:00:

ActiveCell.FormulaR1C1 = _
"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)*1440,0),0)"

Here is the formula I am using for the Rest:

ActiveCell.FormulaR1C1 = _

"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28],(VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)"

How can I get those into the If Then statements recommended?


"Stefi" wrote:

If Range("G1").Value TimeSerial(0, 0, 0) _
And Range("G1").Value < TimeSerial(4, 0, 0) Then
' Calculation # 1
Else
' Calculation # 2
End If

Regards,
Stefi

€žScott€� ezt Ã*rta:

I am creating a new column for an Excel program but this column needs to use
two different formulas based on a Start time in column G. If the Start time
is before midnight then I use one fomula and if the start time is after
midnight but before 4 a.m. then I use a different formula. The formulas work
perfect but I can't seem to write the initial condition correctly so that it
knows which formula to use.

Essentially, how could I write an If Then statement that says if the scan
time in cell ( #, G ) is 24:00 and < 04:00 then

Calculation # 1

Else

Calculation # 2

If Range("G1").Value = Date + TimeSerial(0, 0, 0) And Range("G1").Value <=
Date + TimeSerial(4, 0, 0) Then
DoEvents
Else
DoEvents
End If

"Scott" wrote:

I am creating a new column for an Excel program but this column needs to use
two different formulas based on a Start time in column G. If the Start time
is before midnight then I use one fomula and if the start time is after
midnight but before 4 a.m. then I use a different formula. The formulas work
perfect but I can't seem to write the initial condition correctly so that it
knows which formula to use.

Essentially, how could I write an If Then statement that says if the scan
time in cell ( #, G ) is 24:00 and < 04:00 then

Calculation # 1

Else

Calculation # 2

Here is all the code I am using in case anyone can help put this in the order
it needs to be. I have tried different configurations but always get an error
of some sort. It also needs to keep moving down row by row and checking the
start time in column G. There is a date as well in column F if that helps. It
has gotten so complex that I can't wrap my brain around it anymore.

Range("AJ2").Select
If ("G1").Value TimeSerial(0, 0, 0) _
And Range("G1").Value < TimeSerial(4, 0, 0) Then
ActiveCell.FormulaR1C1 = _
"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)_
*1440,0),0)"
Else
ActiveCell.FormulaR1C1 = _
"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28], _
(VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)"
End If
Selection.Copy
Range("AJ2:AJ" & Lrow).Select
ActiveSheet.Paste


"Scott" wrote:

I am creating a new column for an Excel program but this column needs to use
two different formulas based on a Start time in column G. If the Start time
is before midnight then I use one fomula and if the start time is after
midnight but before 4 a.m. then I use a different formula. The formulas work
perfect but I can't seem to write the initial condition correctly so that it
knows which formula to use.

Essentially, how could I write an If Then statement that says if the scan
time in cell ( #, G ) is 24:00 and < 04:00 then

Calculation # 1

Else

Calculation # 2

Your code contains some syntax errors.

See the correct version above :

Range("AJ2").Select
If Range("G1").Value TimeSerial(0, 0, 0) _
And Range("G1").Value < TimeSerial(4, 0, 0) Then
ActiveCell.FormulaR1C1 = _
"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)" _
& "*1440,0),0)"
Else
ActiveCell.FormulaR1C1 = _
"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28]," _
& "(VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)"
End If
Selection.Copy
Range("AJ2:AJ" & Lrow).Select
ActiveSheet.Paste

John

Ο χÏ�ήστης "Scott" Î*γγÏ�αψε:

Here is all the code I am using in case anyone can help put this in the order
it needs to be. I have tried different configurations but always get an error
of some sort. It also needs to keep moving down row by row and checking the
start time in column G. There is a date as well in column F if that helps. It
has gotten so complex that I can't wrap my brain around it anymore.

Range("AJ2").Select
If ("G1").Value TimeSerial(0, 0, 0) _
And Range("G1").Value < TimeSerial(4, 0, 0) Then
ActiveCell.FormulaR1C1 = _
"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)_
*1440,0),0)"
Else
ActiveCell.FormulaR1C1 = _
"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28], _
(VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)"
End If
Selection.Copy
Range("AJ2:AJ" & Lrow).Select
ActiveSheet.Paste


"Scott" wrote:

I am creating a new column for an Excel program but this column needs to use
two different formulas based on a Start time in column G. If the Start time
is before midnight then I use one fomula and if the start time is after
midnight but before 4 a.m. then I use a different formula. The formulas work
perfect but I can't seem to write the initial condition correctly so that it
knows which formula to use.

Essentially, how could I write an If Then statement that says if the scan
time in cell ( #, G ) is 24:00 and < 04:00 then

Calculation # 1

Else

Calculation # 2

Remember also that any time value smaller than 4:00
is larger than 24:00.

So the statment "Range("G1").Value TimeSerial(0, 0, 0)"
is superfluous. ;-)

John_John,

I don't see the correct version you are refering to??


"John_John" wrote:

Your code contains some syntax errors.

See the correct version above :

Range("AJ2").Select
If Range("G1").Value TimeSerial(0, 0, 0) _
And Range("G1").Value < TimeSerial(4, 0, 0) Then
ActiveCell.FormulaR1C1 = _
"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)" _
& "*1440,0),0)"
Else
ActiveCell.FormulaR1C1 = _
"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28]," _
& "(VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)"
End If
Selection.Copy
Range("AJ2:AJ" & Lrow).Select
ActiveSheet.Paste

John

Ο χÏ�ήστης "Scott" Î*γγÏ�αψε:

Here is all the code I am using in case anyone can help put this in the order
it needs to be. I have tried different configurations but always get an error
of some sort. It also needs to keep moving down row by row and checking the
start time in column G. There is a date as well in column F if that helps. It
has gotten so complex that I can't wrap my brain around it anymore.

Range("AJ2").Select
If ("G1").Value TimeSerial(0, 0, 0) _
And Range("G1").Value < TimeSerial(4, 0, 0) Then
ActiveCell.FormulaR1C1 = _
"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)_
*1440,0),0)"
Else
ActiveCell.FormulaR1C1 = _
"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28], _
(VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)"
End If
Selection.Copy
Range("AJ2:AJ" & Lrow).Select
ActiveSheet.Paste


"Scott" wrote:

I am creating a new column for an Excel program but this column needs to use
two different formulas based on a Start time in column G. If the Start time
is before midnight then I use one fomula and if the start time is after
midnight but before 4 a.m. then I use a different formula. The formulas work
perfect but I can't seem to write the initial condition correctly so that it
knows which formula to use.

Essentially, how could I write an If Then statement that says if the scan
time in cell ( #, G ) is 24:00 and < 04:00 then

Calculation # 1

Else

Calculation # 2

John_John,

I don't see the correct version you are refering to??

"John_John" wrote:

Your code contains some syntax errors.

See the correct version above :

Range("AJ2").Select
If Range("G1").Value TimeSerial(0, 0, 0) _
And Range("G1").Value < TimeSerial(4, 0, 0) Then
ActiveCell.FormulaR1C1 = _
"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)" _
& "*1440,0),0)"
Else
ActiveCell.FormulaR1C1 = _
"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28]," _
& "(VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)"
End If
Selection.Copy
Range("AJ2:AJ" & Lrow).Select
ActiveSheet.Paste

John

Ο χÏ�ήστης "Scott" Î*γγÏ�αψε:

Here is all the code I am using in case anyone can help put this in the order
it needs to be. I have tried different configurations but always get an error
of some sort. It also needs to keep moving down row by row and checking the
start time in column G. There is a date as well in column F if that helps. It
has gotten so complex that I can't wrap my brain around it anymore.

Range("AJ2").Select
If ("G1").Value TimeSerial(0, 0, 0) _
And Range("G1").Value < TimeSerial(4, 0, 0) Then
ActiveCell.FormulaR1C1 = _
"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)_
*1440,0),0)"
Else
ActiveCell.FormulaR1C1 = _
"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28], _
(VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)"
End If
Selection.Copy
Range("AJ2:AJ" & Lrow).Select
ActiveSheet.Paste


"Scott" wrote:

I am creating a new column for an Excel program but this column needs to use
two different formulas based on a Start time in column G. If the Start time
is before midnight then I use one fomula and if the start time is after
midnight but before 4 a.m. then I use a different formula. The formulas work
perfect but I can't seem to write the initial condition correctly so that it
knows which formula to use.

Essentially, how could I write an If Then statement that says if the scan
time in cell ( #, G ) is 24:00 and < 04:00 then

Calculation # 1

Else

Calculation # 2

Range("AJ2").Select
If Range("G1").Value TimeSerial(0, 0, 0) _
And Range("G1").Value < TimeSerial(4, 0, 0) Then
ActiveCell.FormulaR1C1 = _
"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)" _
& "*1440,0),0)"
Else
ActiveCell.FormulaR1C1 = _
"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28]," _
& "(VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)"
End If
Selection.Copy
Range("AJ2:AJ" & Lrow).Select
ActiveSheet.Paste

I am sorry Scott! My English are terrible. :-)

Please let me know if you still occurs errors

John


Ο χÏ�ήστης "Scott" Î*γγÏ�αψε:

John_John,

I don't see the correct version you are refering to??

"John_John" wrote:

Your code contains some syntax errors.

See the correct version above :

Range("AJ2").Select
If Range("G1").Value TimeSerial(0, 0, 0) _
And Range("G1").Value < TimeSerial(4, 0, 0) Then
ActiveCell.FormulaR1C1 = _
"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)" _
& "*1440,0),0)"
Else
ActiveCell.FormulaR1C1 = _
"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28]," _
& "(VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)"
End If
Selection.Copy
Range("AJ2:AJ" & Lrow).Select
ActiveSheet.Paste

John

Ο χÏ�ήστης "Scott" Î*γγÏ�αψε:

Here is all the code I am using in case anyone can help put this in the order
it needs to be. I have tried different configurations but always get an error
of some sort. It also needs to keep moving down row by row and checking the
start time in column G. There is a date as well in column F if that helps. It
has gotten so complex that I can't wrap my brain around it anymore.

Range("AJ2").Select
If ("G1").Value TimeSerial(0, 0, 0) _
And Range("G1").Value < TimeSerial(4, 0, 0) Then
ActiveCell.FormulaR1C1 = _
"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-R[-1]C[-28],1)_
*1440,0),0)"
Else
ActiveCell.FormulaR1C1 = _
"=IF(RC[-34]=R[-1]C[-34],ROUND(MOD(RC[-29]-MAX(R[-1]C[-28], _
(VLOOKUP(RC[-35],RRInfo,5,FALSE))),1)*1440,0),0)"
End If
Selection.Copy
Range("AJ2:AJ" & Lrow).Select
ActiveSheet.Paste


"Scott" wrote:

I am creating a new column for an Excel program but this column needs to use
two different formulas based on a Start time in column G. If the Start time
is before midnight then I use one fomula and if the start time is after
midnight but before 4 a.m. then I use a different formula. The formulas work
perfect but I can't seem to write the initial condition correctly so that it
knows which formula to use.

Essentially, how could I write an If Then statement that says if the scan
time in cell ( #, G ) is 24:00 and < 04:00 then

Calculation # 1

Else

Calculation # 2