download the new for apple Ablebits Ultimate Suite for Excel 2024.1.3443.1616

How to return a date based on values in other cells To display a date rather than a serial number, apply the desired Date format to the formula cell. For instance, combine the YEAR and TODAY to get a serial number for the first day of the current year.Īnd this formula outputs a serial number for the first day of the current month in the current year:

Instead of specifying the values representing the year, month and day directly in a formula, you can have some or all arguments driven by of other Excel date functions. This is the most obvious use of the DATE function in Excel.įor example, to return a serial number corresponding to 2, use this formula: DATE formula to return a serial number for a date Excel DATE formula examplesīelow you will find a few examples of using DATE formulas in Excel beginning with the simplest ones. Tip. At first sight, supplying negative values in the month or day argument of the Excel DATE function may seem absurd, but in practice it may turn out quite useful, for example in the complex formula that converts a week number to a date.

If the year argument is less than 0 or greater than 9999, a DATE formula will return the #NUM! error.

If the year argument is between inclusive, Excel adds the specified number to 1900.

If the year argument is between 19 inclusive, Excel uses exactly the value you supplied to create a date.

For more details, please see Excel date format. By default, Microsoft Excel for Windows uses the 1900 system where Januis represented by the serial number 1. YearĮxcel interprets the year argument according to the date system set up on your computer. In practice, there may be some unobvious pitfalls that the below tips will help you avoid. The DATE syntax looks crystal clear and straightforward on the surface. The DATE function is available in all versions of Excel 365 - Excel 2007. Month - an integer representing the month of the year, from 1 (January) to 12 (December).ĭay - an integer corresponding to the day of the month, from 1 to 31.

Download the new for apple Ablebits Ultimate Suite for Excel 2024.1.3443.1616