Formula/Macro to put date in date column

Tahas

Board Regular
Joined
Sep 10, 2008
Messages
165
Hello Friends,
I have a table where Column A is the date field which contains multiple dates. From Column B:J, I have blank field names with month/year name. I want excel to auto fill the dates when a date is entered in Column A.
Can somebody help me please?

DateJan-2020Mar-2020Mar-2020Apr-2020Apr-2020May-2020Jul-2020Jul-2020Dec-2020
01/01/2020
31/03/2020
31/03/2020
28/04/2020
28/04/2020
31/05/2020
31/07/2020
31/07/2020
31/12/2020
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,454
Office Version
  1. 365
Platform
  1. Windows
Is this what you mean?
Book1
ABCDEFGHIJ
1DateJan-2020Mar-2020Mar-2020Apr-2020Apr-2020May-2020Jul-2020Jul-2020Dec-2020
201/01/202001/01/2020        
331/03/2020 31/03/202031/03/2020      
431/03/2020 31/03/202031/03/2020      
528/04/2020   28/04/202028/04/2020    
628/04/2020   28/04/202028/04/2020    
731/05/2020     31/05/2020   
831/07/2020      31/07/202031/07/2020 
931/07/2020      31/07/202031/07/2020 
1031/12/2020        31/12/2020
Sheet1
Cell Formulas
RangeFormula
B2:J10B2=IF(EOMONTH($A2,-1)+1=B$1,$A2,"")
 
Solution

Tahas

Board Regular
Joined
Sep 10, 2008
Messages
165

ADVERTISEMENT

What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.
I'm using Office 365
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
I'm using Office 365
Thanks for that, please remember to update you account details & then scroll down & click save.

Another option if you don't want blank cells
+Fluff v2.xlsm
ABCDEFGHIJKLM
1Date01/01/202001/02/202001/03/202001/04/202001/05/202001/06/202001/07/202001/08/202001/09/202001/10/202001/11/202001/12/2020
201/01/202001/01/2020 31/03/202028/04/202031/05/2020 31/07/2020    31/12/2020
331/03/202031/03/202028/04/202031/07/2020
431/03/2020
528/04/2020
628/04/2020
731/05/2020
831/07/2020
931/07/2020
1031/12/2020
11
Master
Cell Formulas
RangeFormula
B2:C2,F2:G2,I2:M2,H2:H3,D2:E3B2=FILTER($A$2:$A$100,($A$2:$A$100>=B1)*($A$2:$A$100<=EOMONTH(B1,0)),"")
 

Tahas

Board Regular
Joined
Sep 10, 2008
Messages
165

ADVERTISEMENT

OMG.... you are amazing... BTW, I am using O365
Sorry again... when I copy the formula across, I only see the data on the the first cell. I even tried to copy your data and paste but except for the first cell all give error messages
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,454
Office Version
  1. 365
Platform
  1. Windows
What error message?

You've quoted your own post, it's not clear which method you're referring to.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
Or is this what you want
+Fluff v2.xlsm
ABCDEFGHIJ
1Date01/01/202031/03/202031/03/202028/04/202028/04/202031/05/202031/07/202031/07/202031/12/2020
201/01/2020
331/03/2020
431/03/2020
528/04/2020
628/04/2020
731/05/2020
831/07/2020
931/07/2020
1031/12/2020
11
Master
Cell Formulas
RangeFormula
B1:J1B1=TRANSPOSE(SORT(FILTER(A2:A100,A2:A100<>"")))
Dynamic array formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,597
Messages
5,625,729
Members
416,130
Latest member
galgozzi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top