Using Left function, returning numbers rather than text

[Badnames]

I'm using the left function and want the formula to return the following as text. However, the formula is returning numbers. Not quite sure how it is getting these numbers either. Here is an example using cell C3 value and the number returned.

C3 = 5/14/2014 2:50:26 PM

my formula:

=left(c3,4)

returns:

4177


Is this right? I was trying to get the answer to come out as 5/14

 

[VoG]

Try

=INT(C3)

and format as Custom m/dd

 

[Joe4]

Excel stores all dates and times as numbers (the number of days since 1/1/1900). It can be formatted to display anything you want (if you change the format to General, you will see how Excel stores it).

Just use this instead:
=TEXT(C3,"m/d")

 

[Badnames]

Thanks VoG. What I am trying to use the end result for is to do a comparison to month and day of another column to see where they are the same, the years are different though.

Is there a way I can get the month and date comparison?

If I have in c3:

5/14/2014 2:50:26 PM

and in e3 I have

5/14/2011 2:50:26 PM

Initially I tried just writing:

=left(c3,4)=left(e3,4)


The problem is I was getting different numbers rather than the text string, formatting was not helping either

 

[VoG]

Try


=and(day(c3)=day(e3),month(c3)=month(e3))

 

[Badnames]

Awesome, thank you both! This helped a lot.

 

[scottylad2]

What your seeing in the source cell may not be whats actually in the cell, try formatting the cell as general and see what you have

EDIT - ooops, must have had this page on for a while

 

[Joe4]

Likewise, using my approach, you could also use:

=TEXT(C3,"m/d")=TEXT(E3,"m/d")

 

[Badnames]

What your seeing in the source cell may not be whats actually in the cell, try formatting the cell as general and see what you have

EDIT - ooops, must have had this page on for a while

Yeah, I had tried formatting it multiple ways. For whatever reason the left wouldnt be the same until I formatted "m/d". Not sure why the number would be different, considering they are the same number value (i.e. both 5/14)

 

[Joe4]

Yeah, I had tried formatting it multiple ways. For whatever reason the left wouldnt be the same until I formatted "m/d". Not sure why the number would be different, considering they are the same number value (i.e. both 5/14)

Did you see my original reply?

Excel stores all dates and times as numbers (the number of days since 1/1/1900). It can be formatted to display anything you want (if you change the format to General, you will see how Excel stores it).

Numbers and strings are very different. As I mentioned, dates are stored as numbers, specifically the number of days since 1/1/1900. To see how it is stored in Excel, change the format to General for any date. This is how Excel REALLY sees it. How you format it only changes its appearance, not the underlying values. Functions run out of what is stored, not how it is formatted.

The TEXT function I used converts the value to a String. It tells it to create a string of the date in "m/d" format.