If date and time formatted cell is 2 hours after different date and time formatted

  • I'm trying to write a formula that says,


    if cell containing at date and time in the format of 10/02/2017 13:38 is 2 hours after another cell with a different date and time in the format of 10/02/2017 14:45 then display a 1 otherwise display 0


    Any ideas?


    Also once I have done this, id like the be able to sum up all the 1s. For some reason sum and count is being back 0 in all instances.

  • Re: If date and time formatted cell is 2 hours after different date and time formatte


    One way...


    [COLOR="#0000FF"]
    =IF(B1-A1>TIME(2,0,0),1,0)[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: If date and time formatted cell is 2 hours after different date and time formatte


    Perfect works a treat!


    but i have just noticed, sometimes, the cell doesnt have a date and time in it, sometimes it is blank or has text in it, when this happens i need it to pull back a 0

  • Re: If date and time formatted cell is 2 hours after different date and time formatte


    Try:


    [COLOR="#0000FF"]=IF(OR(A1="",B1=""),"",IF(B1-A1>TIME(2,0,0),1,0))[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!