How does Excel handle composition of functions?

Your formula is correct; it's the way Excel formats cells that is confusing. For example, if you manually set a cell value to 18 and then change the format to a date, it will display that as January 18, 1900.

If you change the format of the cell with your formula to "General", it should display the result you're expecting.

I think you might be confused in thinking that Excel manipulates 'date' values: It doesn't.

How to use dates and times in Excel

In Excel, numbers are used to represent dates (the possibly-fractional number of days since 0-Jan-1900), but they are stored and manipulated by Excel as numbers. TODAY() is a number-valued function, and DAY() is a function from a numeric argument to a numeric result.

In your case, DAY(TODAY()) correctly returned 18 (a number) which I imagine you assigned to a cell that is formatted to show a date in short form - so that 18 displays as 18/01/1900. Because to Excel, when 18 is interpreted as a date, that's what it means.

If you format that cell to a numeric format, you'll see the answer displayed in the way you expected i.e. 18.

Nothing is wrong with your Formula. What I would like to suggest you, if the Formula doesn't produces the expected result then it either produces Error or the Wrong result. Since you have not mentioned what the Formula does have cook !.

Meanwhile I would like to show you what are the possibilities to get DAY value in Number in Excel.

Check the Screen Shot below.

NB:

Date is in Cell E166. 2nd Column F has results and the 3rd Column G shows the Formula I've used for.

You can also ues the TEXT function to get the Day value as Text data.

The Value is RED Colour has Custom Cell format DD, you can apply on the Cell(S) to show only the Day values of the Date(S).

Note, I've suggested you all the possibilities since you have mentioned that basically you are not a Excel user.

Hope this help you, don't forget to post the ERROR your Formula has given.