Formula/Macro to put date in date column

Tahas

Board Regular
Joined
Sep 10, 2008
Messages
166
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.
 
Upvote 0
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,"")
 
Upvote 0
Solution
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)),"")
 
Upvote 0
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
 
Upvote 0
What error message?

You've quoted your own post, it's not clear which method you're referring to.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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
Back
Top