IF Function

clarac

New Member
Can you tell me if you can use a date as a logical test in an IF statement, I have tried to do so with no joy?

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

jeffreybrown

Well-known Member
You could use something like this...

=IF(DATE(2009,3,22) > TODAY(),"Too Late","Too Early")

clarac

New Member
Jeff

Thank you for your reply, unfortunately it does not work. I am trying to calculate if an existing date is in 1 academic year or another, so I adapted you suggestion but but both of these return Last Year when they should display the 2 results. Any further thoughts would be appreciated.

<TABLE style="WIDTH: 197pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=262 border=0 x:str><COLGROUP><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_1872370 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 67pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=89 height=17 x:num="39691">31/08/2008</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 82pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=109 x:str="'=IF(DATE(2008,8,31) <= F2,"Last Year","This Year")">=IF(DATE(2008,8,31) <= F2,"Last Year","This Year")

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39721">30/09/2008</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:str="'=IF(DATE(2008,8,31) <= F3,"Last Year","This Year")">=IF(DATE(2008,8,31) <= F3,"Last Year","This Year")</TD></TR></TBODY></TABLE>

redexodia

New Member
what format does it have to be in? can you just use the date numbers? eg

=if(39691<=F2,"Last Year","This Year")
F2 been your date to test. it actually works without doing the digits for the date.

Also cell reference the date is easier when compairing things so te syntax would simply be a normal if statement

=if(A1<=F2,"","")

Hope that helps

clarac

New Member
redexodia

Thanks, I would prefer to to it displaying as a date format as this is how the original data is stored and extracted from a CSV file.

I have that as a back up if I can not use the data in the format it is in.

Regards
Clarac

c_m

Well-known Member
Jeff

Thank you for your reply, unfortunately it does not work. I am trying to calculate if an existing date is in 1 academic year or another, so I adapted you suggestion but but both of these return Last Year when they should display the 2 results. Any further thoughts would be appreciated.

<table style="width: 197pt; border-collapse: collapse;" x:str="" border="0" cellpadding="0" cellspacing="0" width="262"><colgroup><col style="width: 67pt;" width="89"><col style="width: 48pt;" width="64"><col style="width: 82pt;" width="109"></colgroup><tbody><tr style="height: 12.75pt;" height="17"><td class="xl24" id="td_post_1872370" style="border: medium none rgb(236, 233, 216); width: 67pt; height: 12.75pt; background-color: transparent;" x:num="39691" align="right" width="89" height="17">31/08/2008</td><td style="border: medium none rgb(236, 233, 216); width: 48pt; background-color: transparent;" width="64">
</td><td class="xl25" style="border: medium none rgb(236, 233, 216); width: 82pt; background-color: transparent;" x:str="'=IF(DATE(2008,8,31) <= F2,"Last Year","This Year")" width="109">=IF(DATE(2008,8,31) <= F2,"Last Year","This Year")

</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl24" style="border: medium none rgb(236, 233, 216); height: 12.75pt; background-color: transparent;" x:num="39721" align="right" height="17">30/09/2008</td><td style="border: medium none rgb(236, 233, 216); background-color: transparent;">
</td><td class="xl25" style="border: medium none rgb(236, 233, 216); background-color: transparent;" x:str="'=IF(DATE(2008,8,31) <= F3,"Last Year","This Year")">=IF(DATE(2008,8,31) <= F3,"Last Year","This Year")</td></tr></tbody></table>

what do you mean by "display the 2 results"?

raghavcacs

Board Regular
Hi

I hope the below formula will also solve your problem...

=IF(DATE(2008,12,31)<=DATE(YEAR(A1),MONTH(A1),DAY(A1)),"Last year","This Year")

where A1 is the cell which contains date as 31/08/2008

clarac

New Member
Hi Raghavan

With a small small tweak it worked thank you.

Regards

Clarac

Replies
4
Views
337
Replies
2
Views
387
Replies
3
Views
166
Replies
1
Views
162
Replies
1
Views
73

1,195,670
Messages
6,011,074
Members
441,581
Latest member
rp4717

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back