Forum Discussion

BSeger's avatar
BSeger
Copper Contributor
Jun 04, 2024

Needing help with the TEXT function

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??

 

Resources