This website is one of the best Excel resources I have found. It is likely going to have the answer to your question.
Your expression, =day(today()) is doing what it is supposed to. It returns the number (24) that corresponds to the day in today’s date, not a date. I don’t know what your macro is doing, but the embedded function =today() will return a date – it is probably what you want to use.