We help IT Professionals succeed at work.

# Autopopulate a calendar?

on
91 Views
I got a requirement to auto-populate an Excel with Calendar based on a Date's value (let's say A1)

So, if A1 = 1 Nov 2017, the excel should auto-populate a calendar in Nov 17; same if A1 = 1 Dec 2017, the excel should auto-populate a calendar in Dec 17, and so forth.

Is that possible by using pure Excel formula? I don't need a complex solution here.

Thank you.
test1.xlsx
Comment
Watch Question

## View Solutions Only

Finance Analyst
CERTIFIED EXPERT

Commented:
With this formula in B15 you can identify the Monday prior to the 1st of the month specified, or if the 1st is the Monday then that date:

=IF(WEEKDAY(A1,1)=2,A1,FLOOR(A1,7)+2)

You can then use =B15+1 in C15 and copied across to F15
Then use  =B15+7 in B16 and copied across and down to F19.

You can then use Conditional formatting to ensure that surplus days prior to 1st of month or after end of month are formatted as white font on white background with this formula:

=OR(B15<\$A\$1,B15>EOMONTH(\$A\$1,0)) and apply format of white text.

I will upload a sample shortly.
Finance Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)

Commented:
Thank you both, very nice solutions!
Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Trowa! Glad we could help.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

###### Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

Deciding to stick with EE.

Mohamed Asif

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
###### Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE