[Solved] Filter year & ignore MM DD

Discuss the spreadsheet application

[Solved] Filter year & ignore MM DD

Postby morphingstar » Thu Jul 18, 2019 8:19 pm

Filter year & ignore MM DD
Col A: Date with format YYYY-MM-DD
Col B: Date with format YYYY
formula in B: =A_ _is equal row number
Set Data range and autofilter.
Click filter arrow above first date in column B:
Full date is shown. Selecting just the year is impossible.
I need to filter select the year, independent of MM DD and sort order.

May be the YYYY could be extracted and converted to text?
The column B should remain useful for filter and sorting.

Guidance? Thanks.
Last edited by RusselB on Thu Jul 18, 2019 11:21 pm, edited 1 time in total.
Reason: Green check and Solved tag added
OO 4.1.6 on Win10. Member since StarOffice 7. Alternative name "morphingstar2" when other fails. Use Writer, Calc (as DB + Calc), Draw. Impress mainly for Eyedropper and animating GIFs. Updated 20190716.
User avatar
morphingstar
 
Posts: 65
Joined: Mon Mar 28, 2011 5:52 am
Location: Mx

Re: Filter year & ignore MM DD

Postby RusselB » Thu Jul 18, 2019 8:24 pm

Change your formula for column B to
Code: Select all   Expand viewCollapse view
=year(A1)
and reset the formatting in column B, otherwise you what you see will not look correct.
I used A1 in the formula, but adjust it for the appropriate row(s)
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5282
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Filter year & ignore MM DD

Postby FJCC » Thu Jul 18, 2019 8:24 pm

Your column B should contain the formula
Code: Select all   Expand viewCollapse view
=YEAR(A2)

That will give you the year of the date in A2 as a number. You can then filter and sort on it.
Changing the format of a cell does not change the value, so formatting a date as YYYY does not cause functions to see only the year.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7220
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Filter year & ignore MM DD

Postby morphingstar » Thu Jul 18, 2019 8:46 pm

Marvellous, many thanks
=year(A1)
=month(A1)
=day(A1)
Last edited by RusselB on Thu Jul 18, 2019 11:22 pm, edited 1 time in total.
Reason: Green check removed as it was placed on wrong post.
OO 4.1.6 on Win10. Member since StarOffice 7. Alternative name "morphingstar2" when other fails. Use Writer, Calc (as DB + Calc), Draw. Impress mainly for Eyedropper and animating GIFs. Updated 20190716.
User avatar
morphingstar
 
Posts: 65
Joined: Mon Mar 28, 2011 5:52 am
Location: Mx


Return to Calc

Who is online

Users browsing this forum: AWoodShed and 9 guests