# 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!