IF and MID

**jostre** · 03-14-2016, 01:01 PM

Hi,

I'm importing information from Outlook and one of the columns I need has the date. However, no matter how I format the column with the Date info, Outlook formats some of the dates as Mon 2/29. I'm trying to pull the date out, so I'm using the following formula

=MID(C2,5,4)

That is returning 2/29. However, the remainder of the dates in the column show up as (ie) 2/27/16.

I'm looking for a formula that will just return the number of the month for the whole column. How do I add an IF statement that utilizes my MID statement?

**ChemistB** · 03-14-2016, 01:07 PM

=MID(C2,5,4)+0

The MID function returns a string ("2/29"). The +0 forces excel to look at it like a date. Format the cell as date.

**jostre** · 03-14-2016, 01:17 PM

Hi,

Your formula correctly formatted my date. However, as in the example below, the dates in COL A eventually is formatted correctly (ie) 2/27/16. When I enter your formula in COL B, it converts the correct date to what you see below...

COL A COL B
Mon 2/29 2/29/16
Sun 2/28 2/28/16
2/27/16 1/7/00

If the date is formattted X/XX/XX, i want it to show the date as is. However, if is has (ie) Mon 2/29, I want the name of the date removed. I hope this makes more sense.

**jostre** · 03-14-2016, 01:19 PM

Ideally, it should look like this...

Mon 2/29 2/29/16
Sun 2/28 2/28/16
2/27/16 2/27/16

**ChemistB** · 03-14-2016, 01:27 PM

Ahhh, Is the value 2/27/16 (let's say that's in C9) showing as text or a number. To check, in a blank cell, put =ISNUMBER(C9) and see if it's true or false.
If it's true, you can use this formula
=IF(ISNUMBER(C2), C2, MID(C2,5,4)+0)
If it's False, then
=IF(DateValue(C2), DateValue(C2), Mid(C2,5,4)+0)

**ChemistB** · 03-14-2016, 01:29 PM

Sorry, that will give an error if it's not a number

try
=IF(ISNUMBER(C2+0), DATEVALUE(C2), MID(C2,5,4)+0)

**jostre** · 03-14-2016, 01:31 PM

Thank you, that worked! How did you know to use that formula you recommended?

**jostre** · 03-14-2016, 01:32 PM

I went with this:

=IF(ISNUMBER(C2),C2,MID(C2,5,4)+0)

That formatted everything as X/XX/XX which is what I needed to remove the "Mon/Sun" issue and it left the remaining dates in its current format.

**ChemistB** · 03-14-2016, 01:57 PM

How did you know to use that formula you recommended?

I konw that if your cell formatting is not working, then the value being imported is not seen as a date (and all dates are numbers). The format m/d/yy is seen as a date by Excel so those values that were set up that way imported as numbers. So those we can leave alone.

The others require a little manipulation to fix them. Thus we use the IF statement to determine the proper way to handle it.
Hope that helps.