Data-column

%3CLINGO-SUB%20id%3D%22lingo-sub-1434614%22%20slang%3D%22en-US%22%3EData-column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1434614%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3EWho%20can%20help%20me%3F%3C%2FP%3E%3CP%3EHow%20can%20I%20create%20a%20column%20starting%201-1-2020%20with%20366%20days%20to%2031-12-2020%3F%20It%20has%20to%20be%20the%20first%20column%20of%20a%20accountingsite%20about%20the%20year%202020.%20(and%20next%20year%202021)%3C%2FP%3E%3CP%3E(I%20have%20only%20a%20little%20experience%20with%20Excell.%26nbsp%3B%20and%20Windows%2010)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(Venma45)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1434614%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1435037%22%20slang%3D%22en-US%22%3ERe%3A%20Data-column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1435037%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F687052%22%20target%3D%22_blank%22%3E%40Venma45%3C%2FA%3E%26nbsp%3BSet%20A1%20is%20an%20input%20cell%20where%20you%20manually%20enter%20the%20year%20you%20want%20(so%20this%20year%20%3D%202020%2C%20next%20year%20you%20will%20enter%202021%2C%20etc)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20column%20where%20you%20want%20to%20have%20your%20dates%20(remember%20to%20format%20that%20column%20as%20Date)%2C%20say%20B%2C%20enter%20first%20cell%20B5%3D%20Date(%24A%241%2C1%2C1)%2C%20which%20means%201%2F1%2Fthe%20year%20you%20entered%3C%2FP%3E%3CP%3EThen%20cell%20below%20(B6)%3DIF(YEAR(B5%2B1)%26lt%3B%26gt%3B%24A%241%2C%22%22%2CB5%2B1)%3C%2FP%3E%3CP%3EDrag%20the%20formula%20in%20cell%20B6%20down%20until%20you%20see%2031%2F12%2F2020%2C%20that%20is%20366%20days%20of%20a%20day%20which%20is%20always%20the%20highest%20possible%20days%20in%20a%20year.%20Next%20year%20if%20you%20only%20have%20365%20days%2C%20the%20366th%20row%20would%20show%20a%20blank%20instead%2C%20until%202024%20you%20will%20have%20that%20row%20shows%2031%2F12%20date%20again.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1435144%22%20slang%3D%22en-US%22%3ERe%3A%20Data-column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1435144%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F687052%22%20target%3D%22_blank%22%3E%40Venma45%3C%2FA%3E%26nbsp%3BAs%20a%20variant%2C%20without%20using%20formulae.%20Consider%20using%20Fill%2C%20Series.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnter%20the%20first%20date%20for%20the%20year%20where%20you%20want%20the%20date%20column%20to%20start%20and%20select%20that%20cell.%26nbsp%3BOn%20the%20HOME%20ribbon%20find%20the%20Fill%20icon%20and%20select%20Series...%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-06-03%20at%2006.06.15.png%22%20style%3D%22width%3A%20202px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F196099i245DA4365B1CB25B%2Fimage-dimensions%2F202x288%3Fv%3D1.0%22%20width%3D%22202%22%20height%3D%22288%22%20title%3D%22Screenshot%202020-06-03%20at%2006.06.15.png%22%20alt%3D%22Screenshot%202020-06-03%20at%2006.06.15.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EFrom%20there%2C%20check%20the%20correct%20buttons%20as%20shown%20below.%20Set%20the%20%22Step%20value%22%20to%201%20and%20enter%20the%20%22Stop%20value%22%2C%20being%20the%20last%20day%20of%20the%20year.%20Press%20OK%20and%20Excel%20will%20fill%20the%20column%20below%20your%20active%20cell%20with%20dates%20until%20it%20reaches%20the%20stop%20value.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-06-03%20at%2006.09.01.png%22%20style%3D%22width%3A%20304px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F196100i3E1C64E80505B17F%2Fimage-dimensions%2F304x307%3Fv%3D1.0%22%20width%3D%22304%22%20height%3D%22307%22%20title%3D%22Screenshot%202020-06-03%20at%2006.09.01.png%22%20alt%3D%22Screenshot%202020-06-03%20at%2006.09.01.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello, 

Who can help me?

How can I create a column starting 1-1-2020 with 366 days to 31-12-2020? It has to be the first column of a accountingsite about the year 2020. (and next year 2021)

(I have only a little experience with Excell.  and Windows 10)

 

(Venma45)

2 Replies
Highlighted

@Venma45 Set A1 is an input cell where you manually enter the year you want (so this year = 2020, next year you will enter 2021, etc)

 

In the column where you want to have your dates (remember to format that column as Date), say B, enter first cell B5= Date($A$1,1,1), which means 1/1/the year you entered

Then cell below (B6)=IF(YEAR(B5+1)<>$A$1,"",B5+1)

Drag the formula in cell B6 down until you see 31/12/2020, that is 366 days of a day which is always the highest possible days in a year. Next year if you only have 365 days, the 366th row would show a blank instead, until 2024 you will have that row shows 31/12 date again.

 

Highlighted

@Venma45 As a variant, without using formulae. Consider using Fill, Series.

 

Enter the first date for the year where you want the date column to start and select that cell. On the HOME ribbon find the Fill icon and select Series...

Screenshot 2020-06-03 at 06.06.15.png

From there, check the correct buttons as shown below. Set the "Step value" to 1 and enter the "Stop value", being the last day of the year. Press OK and Excel will fill the column below your active cell with dates until it reaches the stop value.

Screenshot 2020-06-03 at 06.09.01.png