IF DATE and TIME

[AtTcQ]

I currently use this to check if date has passed but i also need to set a time on it too. Is it possible?

=IF(B:B>TODAY(),"Not Passed","Passed")

 

[AtTcQ]

￿￿

 

[AtTcQ]

 

[AtTcQ]

As you can see EVENT 5 should say not passed TIMEVALUE doesnt seem to work

 

[AtTcQ]

Event 6*

 

[MARK858]

You could upload to a file sharing site and post the link in the thread but it is preferred that you have the data actually in the thread as some people can't/won't download files so you are restricting who will look at your post.

��

see the link below about the 2 squares.

2 Squares

Please don't post images as we can't copy/paste them into Excel and it is a pain retyping data (and I often won't). I also can't see your times in column B in your post.

 

[AtTcQ]

XL2BB doesnt paste to clipboard

 

[AtTcQ]

That is weird how come explorer causes that

Book1
	A	B	C	D	E
1	EVENT 4	01/01/2020	PASSED
2	EVENT 5	12/01/2020	NOT PASSED
3	EVENT 6	11/01/2020	PASSED	WITH TIME	CURRENT TIMEDATE 11/01/2020 00:57
Sheet1

Cell Formulas
Range	Formula
C1, C3	C1	=IF(B1>TODAY()+TIMEVALUE("10:00"),"NOT PASSED","PASSED")
C2	C2	=IF(B:B>TODAY(),"NOT PASSED","PASSED")

 

[MARK858]

As you can see EVENT 5 should say not passed TIMEVALUE doesnt seem to work

It does in what you posted by the way you have =IF(B:B>TODAY(),"NOT PASSED","PASSED") in the cell not =IF(B2>TODAY(),"NOT PASSED","PASSED")

For me it comes out as

Book1
	A	B	C
1	EVENT 4	01/01/2020	PASSED
2	EVENT 5	12/01/2020	NOT PASSED
3	EVENT 6	11/01/2020	PASSED
Sheet4

Cell Formulas
Range	Formula
C1:C3	C1	=IF(B1>TODAY()+TIMEVALUE("10:00"),"NOT PASSED","PASSED")

Which is correct as 12/01/2020 00:00 is later that 11/01/2020 10:00

 

[AtTcQ]

Sorry i meant event 6. I have updated to make it clearer.

Book1
	A	B	C	D	E	F
1	EVENT 4	01/01/2020	8:00:00 PM	PASSED
2	EVENT 5	12/01/2020	9:00:00 PM	NOT PASSED
3	EVENT 6	11/01/2020	8:00:00 PM	PASSED	TIMEVALUE	CURRENT TIMEDATE 11/01/20 01:40AM
Sheet1

Cell Formulas
Range	Formula
D1, D3	D1	=IF(B1>TODAY()+TIMEVALUE("10:00"),"NOT PASSED","PASSED")
D2	D2	=IF(B2>TODAY(),"NOT PASSED","PASSED")

 

[MARK858]

Where I am TODAY() is 11/01/2020 so + 10:00 = 11/01/2020 10:00, if B3 is 11/01/2020 with no time then that is 11/01/2020 00:00.
Which is
=IF(11/01/2020 00:00 >11/01/2020 10:00,"NOT PASSED","PASSED") which equates to FALSE therefore "PASSED"
If it was B3+C3> then you would get "NOT PASSED" as in D4 below.

P.S. I don't understand the relevance of the red text in F3 as you aren't using the current time/date or NOW() anywhere.

Book1
	A	B	C	D
1	EVENT 4	01/01/2020	8:00:00 PM	PASSED
2	EVENT 5	12/01/2020	9:00:00 PM	NOT PASSED
3	EVENT 6	11/01/2020	8:00:00 PM	PASSED
4				NOT PASSED
Sheet5

Cell Formulas
Range	Formula
D1:D3	D1	=IF(B1>TODAY()+TIMEVALUE("10:00"),"NOT PASSED","PASSED")
D4	D4	=IF((B3+C3)>TODAY()+TIMEVALUE("10:00"),"NOT PASSED","PASSED")