Creating a calendar in excel that will fill in dates based on month selected

Question

Hello, I'm wondering if there is a way to have a calendar fill in the correct dates when you choose a month and year. I have a data validated list of months but need to find out how to make the day and date , as Saturday 12th. Any help is appreciated. Thank you

Answer 1

Hi, 

Try to keep it simple, something like this:

After that, just format youR dates to have only dates or months visible:

If you need help with that, you can reed more on formatting date and time here. 

Br,

Answer 2

Your screen capture does not make any sense to me.

Where are you going to be inputing the month?

Where do you want to see the dates displayed?

Can you give us a manually typed example of what you want done. Clearly identify where inputs go and where generated dates go.

This article and video shows how to use autofil to generate dates

! AutoFill and Flash Fill (intro) 1min47https://support.office.com/en-us/article/video-use-autofill-and-flash-fill-2e79a709-c814-4b27-8bc2-c4dc84d49464 ****Watch this video for a quick introduction to AutoFill and Flash Fill, two helpful time savers that we’ll cover in more detail in this course.

. https://support.microsoft.com/en-us/office/fill-data-automatically-in-worksheet-cells-74e31bdd-d993-45da-aa82-35a236c5b5db?ui=en-us&rs=en-us&ad=us

.

Auto Fill hidden tricks in Excel          2019 09 24-

https://office-watch.com/2019/auto-fill-magic-in-excel/

Typing or formatting cells of repetitive data can be very boring, but Excel has an Auto Fill feature to make it easier by automatically filling in data for you. We thought we knew Auto Fill but it has some surprising and useful tricks.

Auto Fill goes beyond simple incrementing numbers or dates which is what most of us do. Auto Fill can be used for any type of data that fits a regular pattern.  Dates are well supported with good options under a little-used menu as well as copying formatting choices.

.  *  Numbers

.  *  Dates – day by day

.  *  Dates – week by week

.  *  Dates – monthly or more

.  *  Repeating Formatting

.  *  Auto-Fill Options

.     *  Copy Cells will copy both the format and the content of the cells. This is the default.

.     *  Fill Formatting Onlywill copy the sequence of colors, but without the content.

.     *  Fill Without Formattingwill copy the content of the cells but leave the cells with no color fill.

.     *  Flash Fill is not relevant in this case but we’ve talked about Flash Fill here.

.  *  More Date Auto Fill options

.     *  Copy Cells will again copy exactly whatever cells you have selected. (So if you have only selected one date, the same date will be repeated in every cell. If you have selected a whole sequence, that sequence will be repeated.)

.     *  Fill Series and Fill Days will fill the cells exactly as described in our first example – incrementing by one if you have only selected one cell, and repeating the sequence if you have selected a whole sequence.

.     *  Fill Weekdays will skip dates that fall on Saturday and Sunday.

.     *  Fill Months will fill the cells with the same date of each month.

.     *  Fill Years will fill the cells with the same date each year.

.     *  Last day of the month

.  *  Using Auto Fill for Calculations

.

---

****************** UPLOAD EXAMPLE - TROUBLE SHOOTING - SHARE PERSONAL ONEDRIVE FILE (NOT BUSINESS ONEDRIVE)

.

Trouble shooting problems in files can be like a visit to the dentist, a long, slow painful process of us trying to “extract” the clues needed to recognize the problem in a back and forth flow of questions and answers so that we can come up with a / “the” solution.

.

Often it is faster and easier for everyone if we have a “sample file” get "hands on", to look at, and to “play with”.

.

This next link provides some tips on setting up a sample file and specific instructions for uploading and "sharing" it for us to access:

.

https://answers.microsoft.com/en-us/windows/forum/windows_other-winapps/trouble-shooting-share-onedrive-file/a231a097-bcbf-4e34-ad6c-a33118baf471

The article includes links to macros to randomize text in Word and numbers in Excel to preserve privacy

**************************************

.

This article describes another way of sharing Office files:

2020 03 09- Share a cloud stored document from Microsoft Office

https://office-watch.com/2020/share-cloud-stored-document-microsoft-office/

Using “Invites” to specific people from inside Office apps. Modify document access permissions and add a “note” with “how to” instructions for recipient

.

Answer 3

Hi CrystalCannon,

I apologize for all the trouble it has caused and sorry about your time.

As per your description, I search a lot and here I find article you may refer and see if help.

How To Create A Dynamic Monthly Calendar In Excel?

Automatically updated calendar using formulas

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.

I appreciate your patience and understanding.

Best Regards

Waqas Muhammad

Answer 4

Hello Rohn, You're very correct, I was not at all clear in my question. In the screenshot below, I would want to put the month in cell V4. Then I would like the date and day and month to fill  in cells C8 for date, E8 for day and E9 for month as demonstrated below in those cells. I want that same data to appear in all the headers in a to ah8, a weeks worth. Thanks for your response and I hope this makes my question clearer.

Answer 5

OK, you also need a year with the month to be able to get day of week, monday etc.

If you are only generating a weeks worth, then you also need a starting day, along with the month, and year ...

It appears you are using cell merging, rather a problem for other processing.

What column are you using to enter data between "day of week, day of month and "total carbs". 

I'm guessing you are entering number of carbs in column C, and type/name of carb in column d(?)

What are you going to do with the rows of data with carb quantities and carb types?  Are you keeping it for future reference, or is it disposable, one-time use?

If it is a disposable, one-time use "chart" the simplest approach may be to enter your full starting date in one place, then copy it around and format it for display.

Lots of manual work to set the onetime table up initially.  I have created a rough layout of the first day of week.  You can look at it here in my example workbook, in tab "CrystalCannon2":

https://1drv.ms/x/s!Am8lVyUzjKfpowARghSdfg1koqJf?e=H0lKb5 

I included more specific instructions for setting up the initial elements in the worksheet.

FAST and DIRTY, it looks like this

On the other hand, if you want to keep the data for reference it requries a totally different setup.  A simple input table, then use a PivotTable with a slicer to format and display the information (I think I can make that work).  It is trivially easy to add new data to the input then refresh the pivot table to update the displayed data.

Here are links to articles describing specific parts of the example

Insert Table

!02 Overview of Excel tables****https://support.office.com/en-us/article/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c

To make managing and analyzing a group of related data easier, you can turn a range of cells into an Excel table (previously known as an Excel list).

.

!04 Excel Tables at excelcampus-Tutorial Beginners Guide for Windows 2007-2013& Mac 2011.mp4  16min (downloaded) (and example files)

https://www.excelcampus.com/tables/excel-tables-tutorial-video/ -------------------------------------------- 2013 10 01        Jon Acampora

10 Awesome Reasons to Use Excel Tables

  1. Automatic Formatting ------------------------             2. Organizing & Naming Data

  3. Sorting & Filtering -----------------------------           4. Auto Expansion & Navigating

  5 Total Row----------------------------------------         6. Remove Duplicates

 7. Create Unique List ----------------------------              8. Pivot Table Integration

 9. Chart Integration -------------------------------         10. Table Formulas (Structured References)

.

Custom date formatting to setup date display:

! Custom Date Formats in Excelhttps://www.myexcelonline.com/blog/custom-date-formats-in-excel/

Custom date formats in Excel allow you to display only certain parts of the date.

Say you had a date of 18/02/1979, which coincides to be my birthday.

You can use the Format Cells dialogue box to show only the number 18, the day that corresponds to that date (Sunday), the month as a number on in abbreviated form and the year in two or four digits.

You cam also mix and match to create a custom date format or even enter a custom text that would show something like: Today is Sunday

You can download the following workbook which shows you the different formats that you can use and see the tutorial below of how this can be easily achieved.

DOWNLOAD WORKBOOK

CODE OUTPUT       DATE/TIME  APPEARS AS

m                  Displays the month as a number                                 18/02/1979    2

mm               Displays the month as a number with leading zeros  18/02/1979    02

mmm            Displays the month in abbreviated form                       18/02/1979    Feb

mmmm         Displays the month in full form                                    18/02/1979    February

mmmmm      Displays the first letter of the month                            18/02/1979    F

d                   Displays the day as a number                                     18/02/1979    18

dd                 Displays the day as a number with leading zeros          01/02/1979    01

ddd               Displays the day in abbreviated form                           18/02/1979    Thu

dddd             Displays the day in full form                                        18/02/1979    Thursday

yy                 Displays the last two digits of the year                         18/02/1979    79

yyyy              Displays all the digits of the year                                 18/02/1979    1979

mmmm d, yyyy   Displays the month, the date and the year                   18/02/1979    February 18, 1979

mmmm-yyyy Displays the month-year                                             18/02/1979    February-1979

“Today is” dddd   Displays a custom text for the Today function              11/06/2015    Today is Thursday

h                   Displays the hour as a number                                    9:05:13         9

hh                 Displays the hour as a number with leading zeros          9:05:13         09

m                  Displays minute as number                     9:05:13          5

mm               Displays minutes as a number                9:05:13          05

s                   Displays seconds as a number                9:05:03          3

ss                 Displays seconds as a number                9:05:13          03

ss.s              Display tenth of second                          9:05:13.3          13.3

ss.00            Displays hundredths of second               9:05:13.03     13.03

AM/PM         Displays the hour indicating AM or PM    9:55:13          AM

https://www.myexcelonline.com/wp-content/uploads/2015/06/Number-Formats-Dates.gif

.

How to setup the "linked Text Boxes" for your green table title bar line

How to use text boxes in Excel           2020 09 01

https://www.intheblack.com/articles/2020/09/01/text-boxes-excel

Text boxes are an old Excel feature that can provide flexibility to all types of Excel files. You can apply multiple formats to the text within a text box. You can also link a text box to a cell on the sheet to provide dynamically changing text.

.

Text boxes are independent of the Excel grid, and can be positioned anywhere on the sheet.

Text boxes can be used to:

.  *  add instructions to specific areas of a sheet

.  *  provide dynamic headings for reports

.  *  insert large numbers for dashboards

.  *  add detailed commentary to reports

You can format a text box to:

.  *  remove its border so it looks better on a dashboard

.  *  remove the fill colour and make it transparent

.  *  create large, prominent numbers for dashboards

.

.  *  text box linked to cell

.  *  Grouping text boxes

.  *  Keyboard shortcut <ALT><N><X>

.  *  Example

.  *  Resizing tips

.  *  Copying tip

.  *  Linked pictures

.