Date Functions

[MrDB4Excel]

I have a cell (B3 to be precise) that I manually input the current month and day 1. In other words, I type into this cell for the month of June as follows: 6/1/2019 (actually because we are in the year 2019 I do not even enter 2019 when typing as it auto appears after hitting the enter key; obviously most Excel users already know this).
I have formatted the date in the customs area as dddd--dd mmm yyyy which in turn provides, for example, Friday--01 Mar 2019.
What I would like to do is while keeping this format (dddd--dd mmm yyyy) and only typing the month number e.g. 1 for January, 2 for February etc. that it returns the indicated month and day 1 as the result.
This cell is directly linked to a calendar that is looking at this cell's content as the first day of the given month.
Maybe a helper cell is needed. Any ideas on how to accomplish this?
Say for example in another helper cell I simply type the month number and cell B3 sees that input and returns with the appropriate format that the calendar then reads for example as March 1, 2019.
This calendar is a pivot table based on the date that is in B3. When I change the month number in B3 then the calendar updates after it is refreshed. The date in B3 is always with the first day of the month, so the only change that occurs in B3 is the month number.

 

[MrDB4Excel]

I discovered the solution, so this thread can be closed. It is as follows:

I have 4 separate helper cells as follows that I discovered to enhance the calendar created via John MacDougall's YouTube:

In Cell D15 is the Year Function =YEAR(TODAY())
In Cell E15 is the Month Function =MONTH(TODAY())
In Cell F15 is simply the number 1
In Cell B15 is the Date Function =DATE(D15,E15,F15)

Then Cell B3 is set to equal Cell B15 which gets from D15, E15 & F15 the formatted date of 03,01,2019

When January 1, 2020 rolls around then Cell B15 will read 01,01,2020

Credit for the perpetual calendar these formulas and functions create goes to John MacDougall, Microsoft Excel MVP, HowToExcel.org

https://www.youtube.com/watch?v=smcYYMsZcco

 

[steve the fish]

If you just wanted the first day of the current month you can use:

=EOMONTH(TODAY(),-1)+1

or

=TODAY()-DAY(TODAY())+1

 

[MrDB4Excel]

Many thanks, good functions to keep in my arsenal.