10 Must-Know Microsoft Excel Date Functions

Sandeep Agarwal

Date-Featured

Microsoft Excel stores dates as sequential serial numbers and not in the format that we see. By default, 1st January 1900 is serial number 1. So, 10th May 2014 becomes 41769. Storing dates as serial numbers makes it easier for the tool to perform date calculations.

We will have a look at few date formulas now to understand more about serial numbers.

1. DATE

The date function returns the serial number of a given date. Visually you will still see date value. The serial number is stored in the background and you can retrieve that by changing the cell format to number.

Syntax =date(year, month, day)

2. DAY

Given a serial number this function can extract the day of the month. For example, 41769 represents 10th day of a month.

Syntax =day(serial_number)

3. MONTH

Given a serial number this function can extract its month. Again, 41796 represents month number 5.

Syntax =month(serial_number)

4. YEAR

This function is used to extract the year value from the serial number. So, for 41796 it will be 2014.

Syntax =year(serial_number)

There is something interesting you can do with the above functions. You can actually share serial numbers as hidden date codes.

  1. When you want to create one use date function and format cell as number.
  2. When you want to extract use the formula =date(year(sl_num), month(sl_num), day(sl_num))

Date-Example

5. WEEKDAY

This function returns the day of the week from a given serial number. The return value is a number and by default the count starts from Sunday. So, 41769 returns 7 meaning Saturday.

Syntax =weekday(serial_number, [return_type])

For different day references change the return_type entry as per the table.

return_typedayscorresponding numbers
1 or 17Sunday to Saturday1 through 7
2 or 11Monday to Sunday1 through 7
3Monday to Sunday0 through 6
12Tuesday to Monday1 through 7
13Wednesday to Tuesday1 through 7
14Thursday to Wednesday1 through 7
15Friday to Thursday1 through 7
16Saturday to Friday1 through 7

6. TIME

This works like the date function with the difference that it will return the serial number of the time and not the date. Hour, minute and time do not have to be within 24, 60 and 60 respectively. The tool is intelligent enough to calculate it.

Syntax =time(hour, minute, second)

7. HOUR

Given a serial number you can find what hour of the day it represents.

Syntax =hour(serial_number)

8. MINUTE

Minute extracts the minute value from a given time.

Syntax =minute(serial_number)

9. NOW

Want to insert the current date and time to an excel sheet? Just write the ‘now’ function.

Syntax =now()

10. TODAY

If you want only the date and not the time, ‘today’ will help.

Syntax =today()

Conclusion

In MS Excel the data we see as date may not always be a date. It could be simple text at times. And, though it may not make any difference to the eye it does make difference to the software. For anything to be treated as date the cell must be formatted as date and using the date function takes care of the formatting by itself.

The date format is important because in the background the tool stores the values as serial numbers. If not, you will not be able to perform any date and time calculations.

Image Credit: Dafne Cholet

Also See
#calendar#Lists

Join the newsletter