21

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?

40

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.

  • 3
    It might help to explain why the formatting is doing this. Excel stores timestamps as "days since 00:00:00 January 0th 1900", with hours, minutes and seconds as decimals of a day (noon on Jan 1st, 1900 is "1.5"). – Carl Kevinson Jan 18 '18 at 22:08
  • And excel will default to date or time formatting as soon as any kind of date/time value is used. That can either be another cell which is formatted like that or functions like TODAY(). Really annoying when you use them only in decomposition functions and you have to switch the formatting back. – ratchet freak Jan 19 '18 at 10:29
  • 1
    Tangentially relevant: sciencemag.org/news/2016/08/… [One in five genetics papers contains errors thanks to Microsoft Excel] – Darren Ringer Jan 19 '18 at 14:31
  • @DarrenRinger I always read that as "blaming their tools" - the key point of the paper being and remaining vigilant. Similar has happened to me (though not on genetics) and like everyone else I think "Excel changed that figure to ....", while I should be telling myself "damn, I didn't take into account that that figure would change to ....." – Darren Bartrup-Cook Jan 19 '18 at 16:36
9

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.

2

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.

Get Day in Number

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.

1

Your formula is composed correctly. The unexpected result is due to how different types of data are stored by Excel.

There are two similar systems that Excel uses for storing dates, based on a workbook level setting.

The 1900 Date System

In the 1900 date system, the first day that is supported is January 1, 1900. When you enter a date, the date is converted into a serial number that represents the number of elapsed days since January 1, 1900. For example, if you enter July 5, 1998, Excel converts the date to the serial number 35981.

By default, Microsoft Excel for Windows uses the 1900 date system. The 1900 date system enables better compatibility between Excel and other spreadsheet programs, such as Lotus 1-2-3, that are designed to run under MS-DOS or Microsoft Windows.

The 1904 Date System

In the 1904 date system, the first day that is supported is January 1, 1904. When you enter a date, the date is converted into a serial number that represents the number of elapsed days since January 1, 1904. For example, if you enter July 5, 1998, Excel converts the date to the serial number 34519.

By default, Microsoft Excel for the Macintosh uses the 1904 date system. Because of the design of early Macintosh computers, dates before January 1, 1904 were not supported. This design was intended to prevent problems related to the fact that 1900 was not a leap year. If you switch to the 1900 date system, Excel for the Macintosh does support dates as early as January 1, 1900.

These excerpts have been taken from: https://support.microsoft.com/en-us/help/214330/differences-between-the-1900-and-the-1904-date-system-in-excel

The serial number is a simple running count of the number of dates since January 1st, 1900, including the fictitious date February 29th, 1900. The DAY function, however, can additionally be thought of as a data type conversion function. It takes a date serial number as an input and returns an integer as an output. In your example, if we think of 18 as representing a date serial, and count 18 days forwards from January 1st, 1900, we end up with January 18th, 1900.

The simple fix on the front end is to change your cell format to something else like Number or General. This will tell Excel to display it to you as an integer, rather than trying to interpret it as a date serial. On the back end, everything is good to go. The integer result can be used in any other formula and will behave correctly.

As an example, lets say we wanted to create a conditional formula which told us whether or not the 15th has passed in the current month. We can do this using:

=IF(DAY(TODAY())>15,"The 15th has passed","It is not yet the 15th")

It would be much more difficult to accomplish the same result using a date serial, so the DAY function returning an integer ends up being more useful within the context of Excel's common use cases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.