Hi all,
I have a sheet with approx 5000 rows of data.
One column has a date format with days of the week, (ie dd/mm/yy).
I would like to "convert" this to a month and a year so that I can sort and sub-toal by month and year only.
I know that I can change the format to show mm/yy, but is still retains the "day" information so I cannot summarise by month. I have tried changing the format, then using copy "past special-value", but again the field stil retains the "day".
Any suggestions welcome
Many thanks
Stu
Stu, have you thought of conducting your analysis by means of a Pivot Table?
You can easily group your Date field by Month & Year, this saves modifying original values.
Formula wise - if you want consistency - subtract the day value from the date and add 1 to it, eg:
C1: =$A1-DAY($A1)+1
this will apply a common date to all transactions within any given month (ie 1st of Month).
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Hi DonkeyOte..
..many thanks for the spped of your reply...both abswers great..particularly like the formule, (as that is what I was trying to do), even tho I am not sure how it works....but it does!!!
Brill...many thanks...
Stu
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks