CODEWITHSUNDEEP
excelexcel-formulavba
Kenneth O'Donnell
Kenneth O'Donnell

Reputation: 93

Excel formulas Timestamp

Guys I'm having a problem. In the current sheet. In F1 i'm using the follow formula for date the current date.

=TEXT(NOW(), "dd-mm-yyyy")

Sheet will auto refresh every 1 min.

Dates in each cell (A4:A53)13-11-2017,14-11-2017 date going down. Using and if statement to see if current date is true then insert (B1) if false then write nothing. Current formula i am using.

=IF(F1=A4:A53,"B1,"")

It is always saying dates are not true, always false. Even if they are true. Any advise please

enter image description here

Upvotes: 0

Views: 604

Answers (2)

Koby Douek
Koby Douek

Reputation: 16693

In your formula =IF(F1=A4:A53,"true","false"), the condition F1=A4:A53 will always be FALSE since you are comparing a single cell's value to a range. Also, try casting the cell values to a date using the DATE function.

Try changing your formula to:

=IF(DATE(F1)=DATE(A4),"true","false")

Where $A$1 is the cell with today's date.

Upvotes: 1

Jerry
Jerry

Reputation: 71598

I suspect that the dates in the column are actual date values while the one you are creating from the formula is a text value. As such, they will never be equal.

To have them equal, you should change your formula become =INT(NOW()) (to remove the time from the date and time value) or use =TODAY() (which only gives the date and not the time).

Either that, or change your comparison formula to:

=IF(F1*1=A4*1,"true","false")

In most cases, excel will be able to convert a 'text-ified' date when an arithmetic operation is applied on it. If you get errors, then it means excel was not able to convert the date and you should use a format that your excel understands.

Upvotes: 0

Related Questions