Using a helper cell to return a month value

[StedDOOM]

Hi,

I have a column of date windows formatted as "mm/dd--mm/dd" (so, not in an actual "date" format recognizable by Excel), and I want to use a helper cell to pull the first two numbers from that column, and then another column to pull that number, and return it in "mmm" format.

For example, my date window column B looks like this:
01/20--01/29
02/01--02/10

Then, my helper column C, the best function I could find so far is LEFT:
=LEFT(B5,2)
=LEFT(B6,2)

And finally, my month column D, to take the text from column C and put it into "mmm" format
=IF(C5="","",TEXT(C5,"mmm"))
=IF(C6="","",TEXT(C6,"mmm"))

With these in place, the helper column works fine, and returns "01" for row 5 and "02" for row 6. However, no matter what number is in the helper column C, the cell next to it in column D is still returning "Jan" whether the cell it's referencing is "01" or any other number.

Is there a function better equipped for this task than LEFT? Or am I just missing some small detail?

Any help would be greatly appreciated.

 

[Joe4]

There are numerous ways of doing it, but you can use your current method without need of a helper column. There is no reason why you cannot imbed functions inside other functions, i.e.
just use:

=IF(B5="","",TEXT(LEFT(B5,2),"mmm"))

 

[StedDOOM]

There are numerous ways of doing it, but you can use your current method without need of a helper column. There is no reason why you cannot imbed functions inside other functions, i.e.
just use:

=IF(B5="","",TEXT(LEFT(B5,2),"mmm"))

Thanks for such a quick response. You're right, I can imbed and cut out the need for the helper, however this still returns "Jan" in every row, regardless of whether the LEFT function is returning 01 or 02, 03, etc., and I can't seem to figure out why.

 

[Joe4]

Whoops, I missed that your original function wasn't working right and thought you just wanted to get rid of the helper.
Try this instead:

=IF(B5="","",TEXT(LEFT(B5,5),"mmm"))

The issue is that you are trying to format a number as a month. The way that Excel stores dates is a number, specifically the number of days since 1/0/1900.
So any number less than 31 will return January! We need to pull enough out of there so it is recognized as a date.

 

[StedDOOM]

Whoops, I missed that your original function wasn't working right and thought you just wanted to get rid of the helper.
Try this instead:

=IF(B5="","",TEXT(LEFT(B5,5),"mmm"))

Well, now I just feel dumb. That makes perfect sense and definitely fixed the issue. Thank you!

 

[Joe4]

You are welcome. Glad I was to help.
Note that I updated my last response to include a little explanation of the original issue.