Return month based on day of the year

Hello everyone,

I have a sheet with the following values:

280315 085B 10:24 2R

The '085' is the 85th day of the year.
I would like a formula that reads that part of the data, and return the month that day is in, in the "jan", "feb", "mar", etc. format.

Thanks in advance,

Marco

Try

=TEXT("1/1"+MID(A1,8,3)-1,"mmm")


If you want to have the actual date (not just a text string of the month), remove the text function

="1/1"+MID(A1,8,3)-1
Format the cell as a date.

Assuming your code is in cell A1, use this formula:

=CHOOSE(MONTH(DATE(YEAR(TODAY()),1,1+MID(A1,8,3)*1)),"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC")

Thanks Jonmo for the quick solution. It indeed works.
Can you explain me what the "1/1" does in this formula?
I know what the MID part does. Also, thanks for the other option to show the date. I wonder if it also works correctly in a leap year.

Originally Posted by Jonmo1

Try
=TEXT("1/1"+MID(A1,8,3)-1,"mmm")
If you want to have the actual date (not just a text string of the month), remove the text function

="1/1"+MID(A1,8,3)-1
Format the cell as a date.

The format is always the same like this:
280315 085B 10:24 2R
120814 100A 22:21 1R

The 3 digits, in this case 085 and 100, represent the day of the year.
Currently this is indeed for 2014, but it should work every year. Every year the digits restart at 001. (January 1)

Kind regards,

Marco

Originally Posted by Tony Valko

Does your data ALWAYS follow that format?

The day number is ALWAYS a 3 digit string starting at character number 8?

What year is this for? 2014?

So many replies, I cannot keep up. Thanks for all replies!

The 1/1 represents January 1st

When excel is given a Date and the Year is omitted (1/1), then the current year is assumed.

So this formula will always assume the date is in the current year.

Yes, it will work in a leap year (still assuming the date is intended to be in the current year)

I've tried all your suggestions and I noticed something.
I've used the following data (in A1) as example:
211215 090B 12:34 2R

The day of the year in this string is '090', which is the last day of March.
Jonmo's formula results: "mar", which is correct.
AlKey's formula results "mar" as well.
RudiS' formula already shows "apr", which is wrong in this case.

When I change the day to '091' which is April 1st, Jonmo's formula changes to "apr", while the others stay the same.
AlKey's formula changes to "apr" on day '092', which is April 2nd.

Originally Posted by AlKey

Try this one
=TEXT(REPLACE(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",255)),255,255)),4,1,""),"mmm")

	A	B
1	280315 085B 10:24 2R	Mar

Originally Posted by RudiS

Assuming your code is in cell A1, use this formula:
=CHOOSE(MONTH(DATE(YEAR(TODAY()),1,1+MID(A1,8,3)*1)),"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC")

Originally Posted by Jonmo1

The 1/1 represents January 1st
When excel is given a Date and the Year is omitted (1/1), then the current year is assumed.

So this formula will always assume the date is in the current year.
Yes, it will work in a leap year (still assuming the date is intended to be in the current year)

Thanks for explaining. The data is indeed for the current year. It will always be like that when new entries are added. The new entries will always be for the year they were added.
Thanks for the solution!

Originally Posted by Marco-Kun

The new entries will always be for the year they were added.
Thanks for the solution!

Hmm, we may have a misunderstanding here..

The formula will ALWAYS return the date for the current year (when the formula is calculated)
So if you use this formula in December for data that was created in December, They will represent dates in 2014.
Then you save the book, close it etc..
Then come January and you need to look at that December book again, the dates will now show for 2015.

That's correct, didn't think of that.
Perhaps it's an idea to copy/paste special values for all entries that have a 'jan', 'feb', 'mar' entry when closing the file? So that only the values remain instead of the formula so that the data will be correct, no matter what year you open it?

I guess I can figure something like that out with the macro recorder ^^

Originally Posted by Jonmo1

Hmm, we may have a misunderstanding here..

The formula will ALWAYS return the date for the current year (when the formula is calculated)
So if you use this formula in December for data that was created in December, They will represent dates in 2014.
Then you save the book, close it etc..
Then come January and you need to look at that December book again, the dates will now show for 2015.

Yes, either that or..

Is there anything else in the data that indicates which year it is?
It doesn't have to be in the same cell, maybe an adjescent column?

Originally Posted by Jonmo1

Yes, either that or..
Is there anything else in the data that indicates which year it is?
It doesn't have to be in the same cell, maybe an adjescent column?

Hmm yes, there is a column with the year. I cannot look at the workbook at the moment, and I cannot recall if it's next to the cell with the data.

I do know that the current workbook has cells from the year 2013, and 2014.

Originally Posted by AlKey

The formula I have provided calculate from day 0 and it should have been 1 and that is why it was off by one day. Here is the correct one
=TEXT(REPLACE(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",255)),255,255)),4,1,"")+1,"mmm")
Thanks

Alright, thanks ^^ I didn't notice that. Thanks for the reply!

Great, so let's just say B1 has the Year # (just the number, not a date)

=TEXT(("1/1/"&B1)+MID(A1,8,3)-1,"mmm")

Originally Posted by Jonmo1

Great, so let's just say B1 has the Year # (just the number, not a date)
=TEXT(("1/1/"&B1)+MID(A1,8,3)-1,"mmm")

Also, is it possible to return the actual date with this formula? Like in the first one you gave me?
I tried removing the Text function but it didn't work.

For just the date, it's

=("1/1/"&B1)+MID(A1,8,3)-1

Format the cell as a Date.

Thanks! I see what I did wrong. I removed the '()' around the first part too, giving me a weird output.

Originally Posted by Jonmo1

For just the date, it's

=("1/1/"&B1)+MID(A1,8,3)-1

Format the cell as a Date.

Without those parens, it's calculated like this

="1/1/"&B1+MID(A1,8,3)-1

B1+MID() is done first
so if B1 is 2014 and MID(A1,8,3) is 085
Then it becomes 2014 + 085 = 2099

The parens make it do the ("1/1/"&B1) first