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.

    :!:Forum Rules

  • 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.

    :!:Forum Rules

  • 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

      (9.29 kB, downloaded 46 times, last: )
  • 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.

    :!:Forum Rules

  • 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.

    :!:Forum Rules

  • 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

      (9.41 kB, downloaded 42 times, last: )