How to auto fill Year or month ?

navinrb

Board Regular
Joined
Jun 9, 2020
Messages
63
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
I want to know if there is any way to auto fill year or month as i enter only day.i have many dates with same month and year but with different days so i don't want to write full date format.
For example if i input 05 and press enter..it should automatically set date as 05-01-2020
If i enter 07 ..output should be 07-01-2020 ..and so on.
So basically i mean i just want to enter day but month and year is autofilled
Thanks in advance.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,012
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Will it always be January and 2020?
 

navinrb

Board Regular
Joined
Jun 9, 2020
Messages
63
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
Will it always be January and 2020?
No.it would be different. For eg i have hundreds of sale for March 2019. I need to manually enter all invoice dates. So I just want to input only day of sale but month and year should be autfilled. If day of sale is 2nd march i just want to enter 02 but month and year i.e 03-2019 should be autofilled. Is that possible?
 
Last edited:

hnsd24_CN

Board Regular
Joined
Oct 13, 2020
Messages
78
Office Version
  1. 2016
Platform
  1. Windows
You can use Auxiliary cells,just change D1,Then paste date as Values(V)
union.xlsm
ABCD
101-03-2019201931
2
3
4
5
6
Sheet2
Cell Formulas
RangeFormula
A1A1=TEXT(DATE(B$1,C$1,D$1),"dd-mm-yyyy")
 

navinrb

Board Regular
Joined
Jun 9, 2020
Messages
63
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
You can use Auxiliary cells,just change D1,Then paste date as Values(V)
union.xlsm
ABCD
101-03-2019201931
2
3
4
5
6
Sheet2
Cell Formulas
RangeFormula
A1A1=TEXT(DATE(B$1,C$1,D$1),"dd-mm-yyyy")
Sorry that's not what i am looking for.You might be unlear about my question. It seems my query not possible with excel.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,236
Messages
5,576,896
Members
412,752
Latest member
LUIS SAMANO
Top