# Combining 2 if statements

• [FONT=&quot]I have 2 time ranges, i.e. range1=(17:00,23:59), and range2=(00:00,10:00). Along with that i have 2 dates let yesterday and today. i have time in "C" cell, and date in "O" cell. what I want is, if date = yesterday and time is n range 1 or date is today and time is in range 2 it should display as "TRUE". [/FONT]
[FONT=&quot]I have 2 if conditions which are working perfectly for the 2 given conditions.[/FONT]
[FONT=&quot]Kindly guide me how to combine the 2 formulas. [/FONT]
[FONT=&quot]=IF(AND(O3=TODAY(),C3>=A3,C3<=B3),"range2",0)#[/FONT]
[FONT=&quot]=IF(AND(O3=TODAY()-1,C3>=E3,C3<=F3),"range1",0)#[/FONT]
[FONT=&quot]Thanks,[/FONT]
[FONT=&quot]Sourav[/FONT]

• Re: Combining 2 if statements

Like this:

[FONT=&amp]=IF(AND(O3=TODAY(),C3>=A3,C3<=B3),"range2",[FONT=&amp]IF(AND(O3=TODAY()-1,C3>=E3,C3<=F3),"range1",0)[/FONT])[/FONT]

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Re: Combining 2 if statements

[FONT=&amp]Hi AliGW,

The formula you suggested is not responding somehow. I made a few changes in the in the column names.[/FONT]
[FONT=&amp]=IF(AND(O2=TODAY(),N2>=A2,N2<=B2),"range2",IF(AND(O2=TODAY()-1,N2>=E2,N2<=F2),"range1",0))[/FONT]
[FONT=&amp]Could you review the below excel?[/FONT]
[FONT=&amp]I put the formula in the "P" column. N contains time and O contains dates. [/FONT]
[FONT=&amp]What i notice is only "P2" is displaying the result as "range1". However, the result should come as[/FONT]
[FONT=&amp]P2= range1[/FONT]
[FONT=&amp]P3= 0[/FONT]
[FONT=&amp]P4= range1[/FONT]
[FONT=&amp]P5= range2[/FONT]
[FONT=&amp]P6= range2[/FONT]
[FONT=&amp]P7= 0

[/FONT][TABLE="width: 128"]

[tr]

[TD="class: xl66, width: 128, colspan: 2"]Range2[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]00:00[/TD]
[TD="class: xl65, align: right"]10:00[/TD]

[/tr]

[/TABLE]

[TABLE="width: 128"]

[tr]

[TD="class: xl66, width: 128, colspan: 2"]Range1[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]17:00[/TD]
[TD="class: xl65, align: right"]23:59[/TD]

[/tr]

[/TABLE]
[TABLE="width: 250"]

[tr]

[td]

N

[/td]

[td]

0

[/td]

[td]

P

[/td]

[/tr]

[/TABLE]
[FONT=&amp]
[/FONT]

[TABLE="width: 202"]

[tr]

[TD="align: right"][TABLE="width: 64"]

[tr]

[TD="class: xl65, width: 64, align: right"]17:59[/TD]

[/tr]

[tr]

[TD="class: xl65, width: 64, align: right"][/TD]

[/tr]

[/TABLE]
[/TD]
[TD="align: right"][TABLE="width: 74"]

[tr]

[TD="class: xl65, width: 74, align: right"]19-11-2016[/TD]

[/tr]

[/TABLE]
[/TD]

[td]

[TABLE="width: 64"]

[tr]

[TD="width: 64"]range1

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[/tr]

[tr]

[TD="align: right"][TABLE="width: 64"]

[tr]

[TD="class: xl65, width: 64, align: right"]15:00[/TD]

[/tr]

[/TABLE]
[/TD]
[TD="align: right"][TABLE="width: 74"]

[tr]

[TD="class: xl65, width: 74, align: right"]19-11-2016[/TD]

[/tr]

[/TABLE]
[/TD]

[td]

0

[/td]

[/tr]

[tr]

[TD="align: right"][TABLE="width: 64"]

[tr]

[TD="class: xl65, width: 64, align: right"]18:59[/TD]

[/tr]

[/TABLE]
[/TD]
[TD="align: right"]19-11-2016[/TD]

[td]

0

[/td]

[/tr]

[tr]

[TD="align: right"][TABLE="width: 64"]

[tr]

[TD="class: xl65, width: 64, align: right"]00:10[/TD]

[/tr]

[/TABLE]
[/TD]
[TD="align: right"][TABLE="width: 74"]

[tr]

[TD="class: xl65, width: 74, align: right"]20-11-2016[/TD]

[/tr]

[/TABLE]
[/TD]

[td]

0

[/td]

[/tr]

[tr]

[TD="align: right"][TABLE="width: 64"]

[tr]

[TD="class: xl65, width: 64, align: right"]10:00[/TD]

[/tr]

[/TABLE]
[/TD]
[TD="align: right"][TABLE="width: 74"]

[tr]

[TD="class: xl65, width: 74, align: right"]20-11-2016[/TD]

[/tr]

[/TABLE]
[/TD]

[td]

0

[/td]

[/tr]

[tr]

[TD="align: right"][TABLE="width: 64"]

[tr]

[TD="class: xl65, width: 64, align: right"]15:00[/TD]

[/tr]

[/TABLE]
[/TD]
[TD="align: right"][TABLE="width: 74"]

[tr]

[TD="class: xl65, width: 74, align: right"]20-11-2016[/TD]

[/tr]

[/TABLE]
[/TD]

[td]

0

[/td]

[/tr]

[/TABLE]

[FONT=&amp]Thanks,[/FONT]
[FONT=&amp]Sourav[/FONT]

• Re: Combining 2 if statements

[sw]*[/sw]

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Re: Combining 2 if statements

Hi,

Thanks for the quick reply. I have attached the file now.

Let me know if any improvements needs to be done.

Regards,

Sourav

## Files

• sample.xlsx

• Re: Combining 2 if statements

Some of the cells need fixing:

=IF(AND(O2=TODAY(),M2>=A\$2,M2<=B\$2),"range2",IF(AND(O2=TODAY()-1,M2>=E\$2,M2<=F\$2),"range1",0))

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Re: Combining 2 if statements

Thanks it works just perfect.

• Re: Combining 2 if statements

You're welcome!

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Re: Combining 2 if statements

Hi again,

Hope you are doing well. Thanks again for the guidance. Apparently, I require some more of it. The formula is working perfectly, when i am entering values but it fails to perform when i am trying to get the the date and time from a different source with the formula

Text(J2,"dd:mm:yyyy") and
Text(J2,"hh:mm")

I have a series of data where the date and time are merged together and i have to extract it separately from there and in that case the formula is not responding.

Attaching excel for reference. [J column contains the merged format]

Thanks,

Sourav

## Files

• sample.xlsx