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.
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)
Given a serial number this function can extract the day of the month. For example, 41769 represents 10th day of a month.
Given a serial number this function can extract its month. Again, 41796 represents month number 5.
This function is used to extract the year value from the serial number. So, for 41796 it will be 2014.
There is something interesting you can do with the above functions. You can actually share serial numbers as hidden date codes.
- When you want to create one use date function and format cell as number.
- When you want to extract use the formula =date(year(sl_num), month(sl_num), day(sl_num))
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.
|1 or 17||Sunday to Saturday||1 through 7|
|2 or 11||Monday to Sunday||1 through 7|
|3||Monday to Sunday||0 through 6|
|12||Tuesday to Monday||1 through 7|
|13||Wednesday to Tuesday||1 through 7|
|14||Thursday to Wednesday||1 through 7|
|15||Friday to Thursday||1 through 7|
|16||Saturday to Friday||1 through 7|
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)
Given a serial number you can find what hour of the day it represents.
Minute extracts the minute value from a given time.
Want to insert the current date and time to an excel sheet? Just write the ‘now’ function.
If you want only the date and not the time, ‘today’ will help.
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
Last updated on 02 February, 2022
The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.