Excel dat help
Thread Starter
Join Date: Mar 2005
Location: EGCC
Posts: 124
Likes: 0
Received 0 Likes
on
0 Posts
Excel dat help
Guys n gals,
I have a very basic spreadsheet that has various dates down the left hand side. I am looking for a formulae the returns that summarises the data and returns the month that the date is in.
Eg
01/06/2006
21/06/2006
02/06/2006
05/07/2006
08/08/2006
I could like a table next to the data to say:
June 3
July 1
August 1
etc
I think its simple but am having a serious memory lapse. Too many beers on a Friday night i suppose!!
Cheers
I have a very basic spreadsheet that has various dates down the left hand side. I am looking for a formulae the returns that summarises the data and returns the month that the date is in.
Eg
01/06/2006
21/06/2006
02/06/2006
05/07/2006
08/08/2006
I could like a table next to the data to say:
June 3
July 1
August 1
etc
I think its simple but am having a serious memory lapse. Too many beers on a Friday night i suppose!!
Cheers
Join Date: Jun 2006
Location: BRISTOL!
Age: 39
Posts: 526
Likes: 0
Received 0 Likes
on
0 Posts
I too have a little memory lapse, but i think this is simply a formatting solution. Format the day in the format you want, enter 1/1/2007, get 1st Jan 2007. Or if you want 01.01/07 and 1st jan 2007 next to each other make the next colum a replicar of the previous one and just change the formatting. I have just tryed it and it works, suppose there is a right way to do this, or a better way but i cant think, and its been years since i studdied excel...
Thread Starter
Join Date: Mar 2005
Location: EGCC
Posts: 124
Likes: 0
Received 0 Likes
on
0 Posts
XL Formula
If anyone wants to know I have devolped the following formula which seems to do the job.
=CHOOSE(ROW(),"Jan","Feb","Mar","Apr","May","Jun", "Jul","Aug","Sep","Oct","Nov","Dec")&" "&SUMPRODUCT(--(MONTH(($A$1:$A$30))=ROW()))
This first bit is for displaying the month in short in the first 12 rows only.
Rgds
=CHOOSE(ROW(),"Jan","Feb","Mar","Apr","May","Jun", "Jul","Aug","Sep","Oct","Nov","Dec")&" "&SUMPRODUCT(--(MONTH(($A$1:$A$30))=ROW()))
This first bit is for displaying the month in short in the first 12 rows only.
Rgds