Hello @hp127, I'm not an expert by any means but I think I can help with this;

Open FME > Generate > Select reader and writer

1. Date field = year? or just date? (if Date and you need to clean it look at bottom picture)

    

   1. if year > use AttributeFilter
      1. select the year field > type the years you want to filter for
         1. Or you can select Import... for list > pick same table as reader and pick same field it will auto import. Great for 50 different options.

Reader and Writer

AttributeFilter Parameters and Import Wizard

Here is mine after it worked:

AttributeCreator if you need to clean up a date field so you only select Year (this is just a @Left)

@hp127 Sorry I forgot to add how to Write to Excel in my answer:

Writer Excel, name it anything and select the correct save location

double click on the writer and name it the year you want "2006" > drag the AttributeFilter output for 2006 to it.

Right click on the Writer 2006 > Duplicate > double click on the new writer and type 2007 in name > drag 2007 from Filter to writer.

My sample 2005 naming

Attribute Filter to Writers:

That's a great question.

I think I'd be tempted to use the DateTimeCalculator to figure this out. Set the mode to Calculate Interval Between Datetimes. Select your incoming data attribute as the Start Datetime and under End Datetime open the text editor dialog and add @DateTimeNow() which is a function to calculate the current datetime. Set the Result Type (interval) to Years.

Now you know the difference between the original date and now. Add a Tester. If the difference is >6 then you can discard that record.

For the fanout, use the Fanout parameter in the writer parameters. The Fanout Directory is the folder you want to write to. For the Fanout Expression, open the text editor and in there use the year attribute plus the file extension .xlsx

I created a sample workspace. It should be a good starting point for you to figure out what to do in your own workspace. It only checks for 2 years (because of my data) so you'd need to change the Tester, and only outputs one dataset (again because of the data contents).

You can find it on Dropbox at: https://www.dropbox.com/s/jseimxlyr8uff4z/LastSixYears.fmwt?dl=0

Hope this helps

Hmmm, Re-reading the question I think I've given you the six newest years, not the six oldest! There wouldn't be a lot of difference. You'd just need to find the oldest date (a StatisticsCalculator should help there) and adjust the DateTimeCalculator to find the difference between that and the attribute (instead of DateTimeNow and the attribute)

Assuming that the date values in the source Excel spreadsheet are stored with date data type, you can use the SubstringExtractor to extract year (first 4 digits in the date string) from the date value, then select only features in the oldest 6 years (between 2006 and 2011, i.e. older than 2012) with the Tester. Further, the year can also be used to configure feature type fanout in the destination feature type, as in:

If actual values of the "oldest 6 years" in the source dataset could change whenever the workspace runs, this workflow might help you.

@hp127

tcl wrapped in fme datetime functions

@DateTimeDiff(YourDateField, @DateTimeNow(), years)<=6

Thank @takashi for this detailed and perfect solution! Really grateful for your help!

Thank you very much @mark2atsafe, you've helped me understand how to use the @DateTimeNow functions and Fanout parameters. FME is so clever, I'm so new to using it but think it's ace! :-)

Thank you so much for your help everyone, I'd hit a stumbling block but with all your advice I've been able to reach the required output! I really appreciate your input on this :-)