INDIRECT, TEXT, and DATEVALUE function

[dpatete]

I am trying to reverse engineer this formula:

=INDIRECT(TEXT(DATEVALUE($A$3),"mmmm")) Hours

I am unsure why "Hours" is at the end of the formula. The formula references a date in A3 (October 2022), but I am unsure why it needs to be in the "mmmm" format. The result of the formula in the cell is 177. I think it may be trying to figure out how many working hours (8 hours a day Monday-Friday) are in the referenced month, but I want to understand exactly how this formula works.

 

[Joe4]

Welcome to the Board!

Can you show us an example where it is being used, where we can see both the formula and the underlying data?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also, do you have any VBA code in this workbook?
If so, can you post it?

 

[iggydarsa]

This is my guess:

- The user cannot get the month name, maybe because it is in a text format rather than date format, so he is converting the text into a date with DATEVALUE(A3)
- with TEXT "mmmm", he is converting the date into spelled out month name.
- with INDIRECT he is looking at a cell that is named with that month's name. If you look at top left corner there is a box where it shows the selected cell address. If you click on the dropdown next to it, you should see October (and probably other months as well)

That's what I think.

 

[dpatete]

This is my guess:

- The user cannot get the month name, maybe because it is in a text format rather than date format, so he is converting the text into a date with DATEVALUE(A3)
- with TEXT "mmmm", he is converting the date into spelled out month name.
- with INDIRECT he is looking at a cell that is named with that month's name. If you look at top left corner there is a box where it shows the selected cell address. If you click on the dropdown next to it, you should see October (and probably other months as well)

That's what I think.

This is actually really helpful! You are pretty much spot on. I found the cell that INDIRECT is referencing by using the drop down. Thank you!!