Date and Time formula

[QuizToon]

Hi all,

Hope you can help me, i have been trying to find the answer but just cant seem to find it.

I have a column of cells and each contains a date and a time.

There are 2 options for what i want to display.
1. Highlight a cell in green if it has a date and time in the future of the current date and time, and red if the date and time has passed.
2. in the cell to the right it could say ongoing if the time is in the future and outstanding if the date and time has passed. I can use conditional formatting to colour the cells accordingly.

I can get the both ways to work when it is just the date but nothing happens when i include the time in the cell.

I am not clued up on VB

Hope this is a simple fix for someone.

Many Thanks

 

[jtakw]

Hi,

I don't see why it won't work...
BTW, what's supposed to happen if the Date in the Column is same as Today?

Book1
	A	B
1	1/1/2018 14:30	Outstanding
2	5/15/2018 11:30	Ongoing
Sheet45

Cell Formulas
Range	Formula
B1	=IF(A1>TODAY(),"Ongoing","Outstanding")

 

[SpillerBD]

Use conditional formatting, for the value to compare against, use =NOW()
You'll need two conditions. One for the before, one for future date/times.

 

[hotabae]

=IF(A1>TODAY(),"Ongoing","Outstanding")​

Today() returns the current date, but with 00:00 AM for the time.

If you want it to be accurate to the minute, I'd recommend the "Now()" function.

Both of these functions are considered volatile and will only update when something elsewhere in the workbook calculates.

Basically, this means you have to force it to update by doing something (or hitting F9 - Thanks #5 )

Edit: As far as 'seeing' the date and time together - Format the cells with a custom format like so (dd/mm/yyyy hh:mm)

 

[SpillerBD]

Basically, this means you have to force it to update by doing something

Like hitting F9

 

[QuizToon]

Today() returns the current date, but with 00:00 AM for the time.

If you want it to be accurate to the minute, I'd recommend the "Now()" function.

Both of these functions are considered volatile and will only update when something elsewhere in the workbook calculates.

Basically, this means you have to force it to update by doing something (or hitting F9 - Thanks #5 )

Edit: As far as 'seeing' the date and time together - Format the cells with a custom format like so (dd/mm/yyyy hh:mm)

That did it - seems using NOW was the issue with the time part.

Thanks again