Rrave26
asked on
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?
ASKER
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
DATE-.xlsx
DATE-.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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.
I didn't use a formula just changed the cell format
ASKER
So did I. I changed the cell format to custom and used MMMM.
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.
Have you converted your workbook to xlsx, remember csv files do not have the same features as Excel files.
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
=TEXT(I2,"mmm")
Thanks
Rob H
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
=TEXT(I2,"mmm")
Thanks
Rob H
ASKER
I did convert it to XLSX. Let me try that on the CSV file.
ASKER
After identifying what file we worked in the issue resolved the problem.
I used MMMM which returns January, February, etc.
Can you attach a copy
Assuming the date is in B1, then this works =Month(B1).
Try saving the file as xlsx