excel if statements
RH

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel IF statements

Excel IF statements

resolvedResolved · High Priority · Version 2007

Excel IF statements

Hi, having a problem with IF statements and dates

In column “D2” I have a date say 21/10/11, in column “E2” I want to return the words “Working After 24th Dec 2011 else “Left before 24th December 2011”.
I wrote the following IF statement
=IF(D2>=24/12/2011,"Working After 24th Dec 2011","Leaving Before 24th Dec 2011")

But all columns say “Working After 24th Dec 2011” even those that have an end date before 24th Dec. What have I done wrong? What should the statement be?

Thanks in advance for your help. Lisa

RE: Excel IF statements

Hi Lisa,

Thank you for your question.

Please find the following formula using the DATE function where you specify the Year, Month, Day in the Logical Test.

=IF(D1>=DATE(2011,12,24),"Working After 24th December","Leaving before 24th December")

I hope this makes sense and answers your question.

Let me know.

Regards

Simon

RE: Excel IF statements

Hi Simon, I coped your statement exactly and this statement also returns an answer of "Working After 24th December 2011" when the date in D1 is before 24th Dec.. could this be because the date in your formular is in USA format and my date is in UK format? I have tried to change the date in the formular to mirror UK date format DD/MM/YYYY but I cant work out how to do that..

RE: Excel IF statements

Hi Lisa,

Thank you for your response.

Are your dates in american or UK format?

I have attached a spreadsheet showing you my formula and the dates are in UK format. If the date is >= 24/12/2011, it returns 'Working After 24th Dec 2011'

Have a look at my spreadsheet and can you please confirm this is the result you were looking for.

Regards

Simon

Attached files...

Formula.xlsx


 

Excel tip:

Screen Splitters in Excel

Screen splitter icons can be set from the ribbon bar, or dragged from the scroll bars. The icon just above the up arrow on the right scroll bar controls the horizontal splitter; the icon to the right of the right arrow on the bottom scroll bar controls the vertical splitter.

These icons can be double-clicked to split the screen at the top left of the cell currently in use.

View all Excel hints and tips


Server loaded in 0.06 secs.