Needing help with the TEXT function

Copper Contributor

Objective: create a date counter to add 1 day to previous cell and return day of month (number 1 to 31). I also need to be able to reference every cells' date (where the day's number is listed) for other formulas, so it must contain the correct date. 


I have tried a few things but come up with issues:

  • formatting cells does not have a "D" option

  • a regular number counter will not know when to start at "1" for a new month

  • the DATE() and EOMONTH() functions are not really suitable, etc.


The one I have most success with is the TEXT() function, but notice that it does not recognize or change the month if only day is returned (it automatically thinks Jan as the month).


e.g. :

C4 = June 26th, 2024

=TEXT(C4, "mmmm d")    output: June 26        date noted: June 26

=TEXT(C4,"d")                   output: 26                date noted: January 26    <--- this is the issue


Any help??


2 Replies





You may format with D


Fill series of days applying that format, it shall work