Formula to return a True/False if date equals yesterday's date

**abbeycrombie** · 02-05-2013, 02:29 PM

I have "Completed Date" under column M in the format of the following:

2/5/2013 11:23:07 AM (m/d/yyyy h:mm under Custom number format)

I want to add another column next to it that will return the word "TRUE" or "FALSE" if the order was completed the day before regardless of the time that is shown in "Completed Date" so that I can use a filter to show only the TRUE results and the FALSE to be hidden.

I'm not familiar with writing my own formulas so I started with the following:

=IF(TODAY()-1=M2,TRUE,FALSE)

It is returning all FALSE and I believe it's because of the time that is added to the date. This is how the data is exported so I'd rather not change the formats for all the dates to make this work unless I can create another column that can automatically convert the date format to something that is workable for what I'm trying to achieve (ie. changing 02/05/2013 12:26:24 PM to a simply read 02/05/2013).

Not trying to complicate things further, but we only want to include business days so if it was Monday today, yesterday would be considered Friday so we'd want to see Friday's results.

Any help is greatly appreciated. Thanks very much.

**daddylonglegs** · 02-05-2013, 02:31 PM

You can get just the date by using INT function, i.e.

=IF(TODAY()-1=INT(M2),TRUE,FALSE)

.....but you don't really need the IF function, just use

=TODAY()-1=INT(M2)

**tigeravatar** · 02-05-2013, 02:35 PM

abbeycrombie,

You were close, give this a try:
=IF(TODAY()-1=INT(M2),TRUE,FALSE)

Simplified:
=(TODAY()-1=INT(M2))

Also, instead of using the TODAY() function in a formula, I would recommend having a separate cell that contains this formula:
=TODAY()-1

Then just reference that cell in your main formula. Because TODAY() is volatile, it will get recalculated any time there is a change to the workbook. So only having a single cell with that volatile function will save on workbook calc time and resources. If the cell containing the TODAY() function is A1:
=($A$1=INT(M2))

[EDIT]: dll beat me to it

**abbeycrombie** · 02-05-2013, 02:47 PM

Thanks so much to the both of you.

I'm still getting a FALSE return. I was thinking that this might because of the added timestamp to the "Completed Date" column. For instance, entering =TODAY()-1 in the new Column N is now showing me 2/4/2013 0:00 but "Completed Date" under Column M has 2/4/2013 11:23:07 AM. I'm going to assume that these two are not the same even if the actual dates are the same because of the timestamp.

Also, not sure if either of you saw the edit I made, but they want to only see business days so if today was Monday, "yesterday" would be considered Friday. Would this complicate things?

**daddylonglegs** · 02-05-2013, 03:03 PM

Try this

=WORKDAY(M2,1)=TODAY()

WORKDAY will ignore the time in M2 and simply return the next working day (so on Friday it returns the following Monday)

**abbeycrombie** · 12-17-2013, 11:31 AM

Thank you for the suggestions!! This helped me to get my report going and it has slowly evolved into something much more involved. I wouldn't have been able to do it without your help!