Can't get this to work for adding 1 to number and seeing the month

[Fredep57]

So, I need a file that has 12 months for my tracking of medications given. I have a workbook that I have started and have sheets named for the various months along with a "Master" sheet . I want to have the Master sheet "drive" the rest and have each of them have a different month in a cell. In that cell would also be the year (this year but more about that some other time).

In the Master sheet I have the numbers 1 through 12 in column A (A1 through A12).

In the January sheet I have the formula in the image attached. Well, the formula is actually "=TEXT(Master!A1,"mmmm")". However when I put in formula shown in the February sheet it still shows January. This happens to every sheet added if I change the A2 to A3...A12. What am I doing wrong????

As an aside, I would actually like to have the formula "=TEXT(Master!A1+1, "mmmm")" in the next sheet and keep adding down the line. Make sense?? Hopefully.

Thanks
Fred

 

[Tom.Jones]

Upload the file.

 

[Fluff]

In Excel dates are just numbers with a special format, where 1 is the 1st Jan 1900 & 12 is 12th Jan 1900 which is why it does not work.
Put an actual date in A1 namely 1/1/2023 & then for the Jan sheet use

=TEXT(Master!A1,"mmmm")

for Feb use

=TEXT(edate(Master!A1,1),"mmmm")

& for Mar change the 1 to a 2 etc

 

[Joe4]

It is important to understand how Excel stores dates. It stores them as numbers, specifically the number of days since 1/0/1900 (enter any date in any cell, and change the cell format to "General" and you will see the date as Excel does).

So the number 1 really translates to January 1, 1900
number 2 translates to January 2, 1900
etc

If you are trying to build a date from numbers, you could do something a formula this:

=DATE(YEAR(TODAY()),A1,1)

which would return the current year and the month number from cell 1.

You could then format that valid date any way you like, using a Custom Format, or you could apply the Text function to it.

 

[Fredep57]

Thanks guys. However, I stumbled across a thread that fixed my issue.

I now use the "master" sheet and put in the date of 01/01/2023 (or some start date). If I put that in lets say cell A1 on the Master sheet. Then in the next sheet I first formatted the cell I want the Month and Year to "mmmm yyyy". Then in that cell I used the formula =EDATE(Master!A1*29,1) and that gave me "February 2023" in that cell. So by copying this formula into the next sheets and changing the last value to 1 less than the month number I want this worked.

I am still concerned that I had to multiply the original number by 29 but do understand that Excel does numbers different that what I am thinking. It is way smarter than me!!

Again, thanks for all the help and explanations.

Fred

 

[Fluff]

I am still concerned that I had to multiply the original number by 29

You don't, see post#3