split coloumn

F

Finn

I have a coloumn with dates in the format dd-mm-yyyy
I neet to make a sub total for each change in month
I know it is possible to split the coloumn so I can get the month in a
coloumn of its own, but how do I do it
 
J

John Bundy

You can do a Data->Text to columns and split on the "-" but would probably be
better to use =MONTH(A1) and copy it down, copy paste special values, then
subtotal on this column.
 
R

Ron Rosenfeld

I have a coloumn with dates in the format dd-mm-yyyy
I neet to make a sub total for each change in month
I know it is possible to split the coloumn so I can get the month in a
coloumn of its own, but how do I do it
Click to expand...

=month(dt) will give you the month of dt as a number.

You could also use a Pivot Table without changing your data table.

Data/Pivot Table

Drag Dates to the Row area
Drag Amounts (or whatever you want to subtotal) to the Data area.

Then Right-Click in the date column of the pivot table, select to Group and by
Months.

Format to taste
--ron
 
B

Bernard Liengme

If the dates are in there as text, you will be able to use Data | Text to
Column to split ("parse") the data.
But if they are real dates, then they are stored as numbers and only
displayed with the format so parsing is a no go. To get the month use
=MONTH(A1)
best wishes
 
Top