Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 2nd 19, 04:24 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 1
Default YEAR result from an ArrayFormula, or Query function

I have a date in column J [01/01/2019]

I need column AS to show JUST the 2019 Year, and I need (strongly want) a header that says "YEAR". I cant have the formula in the row 2 because of sorting. or can I?

I don't want to drag a formula endlessly down... I would like an arrayFormula or Query solution.

Thank you.

  #2   Report Post  
Old July 2nd 19, 12:30 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2016
Posts: 17
Default YEAR result from an ArrayFormula, or Query function

On Tuesday, 2 July 2019 04:24:40 UTC+1, wrote:
I have a date in column J [01/01/2019]

I need column AS to show JUST the 2019 Year, and I need (strongly want) a header that says "YEAR". I cant have the formula in the row 2 because of sorting. or can I?

I don't want to drag a formula endlessly down... I would like an arrayFormula or Query solution.

Thank you.


In cell AS2 enter =YEAR(J2)
Hover over the bottom right corner of cell AS2 until you see the "+" fill handle. Double click the fill handle and it will automatically fill the formula down the column for you.

Better still, if you could make the range that contains column As into a table (Insert Table) then anter your formula in As2 and it will automatically populate the other cells in column As, and also add the formula s you add any more rows.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlookup with an Arrayformula.... HELP! Vitamin J Excel Discussion (Misc queries) 0 May 22nd 12 06:05 PM
How do I crate a function for week of year + year in same cell. patty ann Excel Worksheet Functions 1 March 16th 08 06:34 PM
CountA and ArrayFormula ron Excel Worksheet Functions 4 November 15th 07 08:51 PM
Importing result from Access query to Excel but the result only c. Edwin Excel Discussion (Misc queries) 0 March 16th 06 01:36 AM
Size of arrayformula with VBA Sune Fibaek[_2_] Excel Programming 4 September 21st 05 09:16 AM


All times are GMT +1. The time now is 03:04 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017