Time series with dates?

Hi,

You can use this function to convert this format --> 20030429 to a date. Try this: Put this on cell b1 20030429, then copy and paste this formula on c1

=VALUE(LEFT(B1,4)&"/"&MID(B1,5,2)&"/"&RIGHT(B1,2))

You can now use the =Year(), =Month(), =Day(), to extract the year/month/day of a date.

Hope that helps.

thanks corine

hmm i dont think thats quite what i'm looking for

e.g
Account Number OPEN DATE
558788889 20030429
558788889 20080512
558788889 20030514
333543543 20020519
558788889 20030522
456446464 20040529
543543535 20030613
543535465 20050618

Ive sorted the data by date and i guess i could manually count how many registrations per year (problem is i have thousands of records)
Ideally i'd like to have some data displaying the number of registrations per year, their decline/increase and then perhaps break down each year and see the monthly trend.

Not quite sure which formula applies.

The attached workbook is a start at analyzing your data. You might go further by defining a pivot table or filter.

The months and weeks are cumulative, so that subtotals operate properly if they are considered as a continuous time series.

Please Login or Register  to view this content.