Formula to calculate month

I have a CSV file that contains record information and some dates.  From this I want to calculate the Month in which the ticket was created.  The cell I am trying to convert or use in the formula resides in column I and looks like this 1/6/2015  3:33:28 AM.  I first tried using the formula =Month(I2) to get the month.  But when I copy that formula to all of the rows, 43713 of them, they all returned the same result, in this case Jan.  I have data in every month should see all of the months up to and including July.  I have spot checked several formula entries and they are using the correct cell for the calculation, in other words it isn't a copy error.  So then I thought I would try just pulling the first entry of the date record, the month number, by using =Left(i2,1).  But this time I got the number 4, which isn't what I would have expected at all.  Any other suggestions I can try?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Why a csv file?

Can you attach a copy

Assuming the date is in B1, then this works =Month(B1).

Try saving the file as xlsx
Rrave26Author Commented:
A CSV file because the file is too large and data get's cut off by doing it any other way. When I saved the file I saved it as an xlsx file but it came in as a CSV file
Roy CoxGroup Finance ManagerCommented:
The cells need formatting as a date. I've formatted the cells as MMMM

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Brian CusterBI ArchitectCommented:
Just create a column that formats the "date" field in your CSV to an actual date and then format the date to just display the month using the notation in the comment above.
Rrave26Author Commented:
Roy that is not working.  When I change column I to the MMMM format when I copy the formula down It report March for dates in Feb as well as reporting dates in Oct, Nov, and Dec, which I have no dates for those months yet.
Roy CoxGroup Finance ManagerCommented:
I didn't use a formula just changed the cell format
Rrave26Author Commented:
So did I.  I changed the cell format to custom and used MMMM.
Roy CoxGroup Finance ManagerCommented:
It certainly worked uding the example that you provided. I've just downloaded it again and it took seconds to select the datae column and format them as dates.

Have you converted your workbook to xlsx, remember csv files do not have the same features as Excel files.
Rob HensonFinance AnalystCommented:
The LEFT(I2,1) is giving number 4 because the serial number behind the date is 42010 for 6 Jan 15.

For the same reason the MONTH formatted as "mmm" isn't working. MONTH gives a number from 1 to 12. When formatted as "mmm" the system is saying to itself "OK, you are telling me to format in a Date related format so this is a date". Date serial number 1 was the 01 Jan 1900, date serial number 7 was 7 Jan 1900; hence why all returning January.

Change your column Q formula to


Rob H
Rrave26Author Commented:
I did convert it to XLSX.  Let me try that on the CSV file.
Rrave26Author Commented:
After identifying what file we worked in the issue resolved the problem.
Roy CoxGroup Finance ManagerCommented:
I used MMMM which returns January, February, etc.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.