| How does Excel store dates? How will excel calculate the difference between two dates? Can I create a formula for this operation? Is there any function in excel which will help to perform this operation with ease. What is its syntax? Please let me know where under Formulas can I find this function. |
Yes, Microsoft Office Excel allows you to enter the date or a different date in a cell. But the difference is that, instead of displaying the actual month in words, it displays the equivalent number or the equivalent value of the month. For example, if the month you wish to display is June, instead of entering the word June, you need to enter 6 because it is the sixth month of the year.
The formula for date is =DATE(year,month,day). So, to enter a specific date on a cell for example March 25, 1988, type without quotes “=date(1988,3,25)” then hit Enter. It should now display the date you entered. See image.
This is how Microsoft Office Excel displays the date by default. But if you don’t like the way the date is displayed, you can always change the format of the date. You can make Microsoft Office Excel display the month in words as well as display the day of the week the date falls. See image.
To change the format of the date, right-click on the cell that contains the date formula then select Format Cells. See image.
In Format Cells dialog box, select Number tab then select Date from the Category. Select the date format you wish to use then click OK. And that’s it. The new format will be applied to your date. See image.
Excel store dates as a number of days from 1900-Jan-0
To calculate the difference we can subtract two cells with ‘-‘ operator containing the dates.
In our case we have dates in cell ‘A3’ and cell ‘A2’
=A3-A2 (Days between the two dates)
=NETWORKDAYS(A2,A3) (Weekdays between the two dates)
=MONTH(A3)-MONTH(A2) (Months occurring between two dates in the same year)
=YEAR(A3)-YEAR(A2) (Years occurring between two dates)
You can find it under the tab of “FORMULAS” then from drop doen menu of “Date & Time”
