#Format date cells in excel not working serial number#
#Format date cells in excel not working plus#

Instead, you need to tweak some settings. To change this format, you do not need a formula.

We discussed earlier that Excel will refer to your computer's date format and uses the same format for displaying dates in a worksheet. But, let's say you want to use different separators for the days (for example, '/' instead of '-'), or you want to use the name of the month instead of a number (for example, January instead of 01). So, you added some neat formulas to your Excel sheet and everything worked out fine. The formula will return the number of days between today (whenever that may be) and March 15, 2001. In its purest form, this is what a DATE formula looks like: Example 1 – The Plain – Vanilla DATE formula Now let's have a look at some of the examples of the DATE function in Excel. These options sit on Excel's Home Tab that appears at the top left. To do this, select the cell containing the returned date and search for the 'Short Date' option in the drop-down menu of cell formatting options in the 'Number group'.

#Format date cells in excel not working serial number#

If you see the Excel DATE function is returning a serial number like 36557 instead of a date, you will need to change the cell's format.

DATE function returns a #VALUE! error when any of its three arguments is supplied with a non-numeric value.

DATE function returns a #NUM! error when year argument is supplied with a negative number or a number greater than 9999.

Important characteristics of the DATE function While in an ideal case, you will enter an integer from 1 to 31 as the date argument, it can be supplied as any other positive or negative integer and Excel will apply the same principles to compute the date as it does for the month argument. For example, if you enter month as -10 and year as 2001, Excel will return a serial number that represents February 01, 2000.ĭay – This is a required argument that represents the day component of the date.

#Format date cells in excel not working plus#

On the other hand, if you enter a value less than 1, Excel will reduce that many months from January of the year specified, plus 1. Note that Excel will return the date as the first date of the interpreted month (June 01 in our case), regardless of what you input in the date argument.

For example, entering year as 2001 and month as 18 will return a serial number that represents June 01, 2002. Ideally, it must be an integer from 1 to 12 (January to December).Īlternatively, if you input a number greater than 12, Excel will count that many months starting from January of the year specified in the year argument.

Month – This is a required argument that represents the month component in the date. Note that entering a negative value or a value greater than 9999 will return a #NUM! error. Enter it as a 4 digit number entering a single digit will result in Excel automatically adding 1900 to it.įor example, entering year as '10' will instruct the formula to interpret the year argument as '1910'.Įxcel refers to your computer's date system set up to interpret the Year argument. Year – This is a required argument that represents the year component in the date.