0

I've a column of dates in my table more than 300.000 rows formatted as dd-mm-yyyy. now I'll from all rows the month in text and the year. How can I do that on the fast way?

Example table:

+----------+------------+----------+---------------+------+
|  Number  |    Date    |   Hours  | Month in text | Year |
+----------+------------+----------+---------------+------+
| 1        | 01-01-2010 | 0:00:00  | January       | 2010 |
| 2        | 01-02-2010 | 0:00:05  | February      | 2010 |
| ...      | ...        | ...      | ...           | ...  |
| 325.696  | 01-12-2014 | 23:59:55 | December      | 2014 |
+----------+------------+----------+---------------+------+

I'm not so good whit working with Excel. So can you give me also a formula to calculate it. Is there also a fast way to calculate that for all the rows?

2
  • 2
    Just out of curiosity, is this a growing document? The reason I am asking is that you have a limit when you reach roughly 1048000 rows, if you plan to use this for a few years you should perhaps plan ahead so it doesn't come as a surprise and create a problem down the line. If the document grows linear, you could keep this up for a long time, but if it is business related and grows exponentially or experience a growth spurt, you might need to migrate to a database and that is always easier to do in a planned manned, not out of panic when you realize that you just hit the bottom of your sheet Nov 27, 2015 at 17:24
  • @JonCarlstedt: The document I have is not growing. First the excel file was a csv file. But for analytic calculations it is easier to use excel. But thanks to say it that there is an limit on the rows you can use in excel. :-) Nov 28, 2015 at 9:52

2 Answers 2

1

I don't have Excel on this machine, so this may not be exactly right, but something like this should work.

=text(a1,"mmmm")

For the year:

=year(a1)

4
  • 1) I'm sorry, I was wrong... :-( It was because, anyone used another formula that was more difficult than that. / 2) How can you slide that formula for more than 300,000 rows? / 3) I've tried to use a comma as separator for the formula but that didn't work. Now I use a dot comma as separator. I'm not sure but I think it is because the different language and notation than the USA. I use the Belgian (European) notation. Nov 28, 2015 at 10:26
  • (2) Are you talking about using that little dot in the corner that you drag? I never use that thing. Copy the formula and paste it into the target cells. Or use control-D to duplicate. (3) As far as I know, a comma is standard to separate items in Excel formulas.
    – Tom Zych
    Nov 28, 2015 at 10:36
  • 2) yes, it is that little dot. Thanks for the hint for duplicate it. 3) Yes, a comma didn't work by me. So if you say, a comma is standard in USA, I believe you. I know that there is a different between comma and dot comma separations in Belgium (Europe) and the USA. Here we use a dot comma also as separation for csv files. In Europe we use a comma for decimal notations. Example: In Europe: 5,3 / In USA: 5.3 Nov 28, 2015 at 10:50
  • Um, that symbol you're calling a “dot comma” is usually known as a “period”.
    – Tom Zych
    Nov 28, 2015 at 11:05
1

If the original date is in cell A1, then in C1 you have to put =A1. Format cells as Custom and put mmmm.

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .