How does Excel handle composition of functions?

I was trying to use functions to get today's day as a number in Excel. For example, when I wrote this question I should have obtained $18$.

I tried to use:

1. the function TODAY() which returns today's date in a date format.
2. Day(<date>) that returns the day number of <date>. That is DAY(19/05/1984) should return 19.

Therefore, the formula

=DAY(TODAY())

should give the result I wanted.

However, this return 18/01/1900 (when using the function today)

If I use

=TODAY()

in cell A1 and then

=DAY(A1)

in another cell, I obtain the right result.

Why does DAY(TODAY()) not give the expected result?

From my background of mathematician and amateur programmer (in Maple, Java, and some Python), my composition of functions seems fine.

More generally, how does Excel handle composition of formulas?

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.