Date Functions

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
153
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
153
Office Version
  1. 2013
Platform
  1. Windows
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
 
Last edited:

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,112
Office Version
  1. 365
Platform
  1. Windows
If you just wanted the first day of the current month you can use:

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

or

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

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
153
Office Version
  1. 2013
Platform
  1. Windows
Many thanks, good functions to keep in my arsenal.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,522
Messages
5,529,329
Members
409,863
Latest member
stacy09
Top