• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 54
  • Last Modified:

Autopopulate a calendar?

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.

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

Thank you.
test1.xlsx
0
trowa
Asked:
trowa
  • 2
  • 2
2 Solutions
 
Rob HensonFinance AnalystCommented:
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.
0
 
Rob HensonFinance AnalystCommented:
See attached.
calrendar-test1.xlsx
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the attached with one simple approach.
Remember that the columns B and H are hidden.
Calendar.xlsx
0
 
trowaAuthor Commented:
Thank you both, very nice solutions!
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Trowa! Glad we could help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now