Excel formulas Timestamp

0
votes

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

2
excelexcel-formulavba
How are the cells formatted? As date-time? Are they formatted with the same date-time format? - Koby Douek
Yip, made sure all cells formatted as date. same for format custom. dd-mm-yyyy. - Kenneth O'Donnell
=IF(F1=A4:A53,"true","false") F1 is were current date is Date running down must be equal to current date - Kenneth O'Donnell
F1=A4:A53 will never be TRUE - you are comparing a single value to a range... - Koby Douek
But even if i do it on the current cell for example =if(F1=A4,"true","false") still is false - Kenneth O'Donnell

2 Answers

1
votes

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.

0
votes

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.