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.
2. DAY
Given a serial number this function can extract the day of the month. For example, 41769 represents 10th day of a month.
3. MONTH
Given a serial number this function can extract its month. Again, 41796 represents month number 5.
4. YEAR
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.
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. For different day references change the return_type entry as per the table.
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.
7. HOUR
Given a serial number you can find what hour of the day it represents.
8. MINUTE
Minute extracts the minute value from a given time.
9. NOW
Want to insert the current date and time to an excel sheet? Just write the ‘now’ function.
10. TODAY
If you want only the date and not the time, ‘today’ will help.
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 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.
