IF Function

clarac

New Member
Joined
Mar 15, 2009
Messages
6
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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>
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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"?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top