Date and Time Functions
Because dates are serial numbers (1 = 1 January 1900), you can add, subtract and compare them. Examples assume today is Wednesday, 21 October 2026.
| Function | Syntax | Example | Result |
|---|---|---|---|
| TODAY | =TODAY() | Current date. Volatile: updates every day. | |
| NOW | =NOW() | Current date and time. | |
| DATE | =DATE(year, month, day) | =DATE(2026,10,21) | 21-10-2026 |
| YEAR / MONTH / DAY | =YEAR(date) | =MONTH(DATE(2026,10,21)) | 10 |
| HOUR / MINUTE / SECOND | =HOUR(NOW()) | The hour, minute or second | |
| TIME | =TIME(6,0,0) | 0.25 (6 AM is a quarter of a day) | |
| WEEKDAY | =WEEKDAY(date, [return_type]) | =WEEKDAY(DATE(2026,10,21)) | 4 (Wednesday) |
=WEEKDAY(DATE(2026,10,21),2) | 3 (Monday = 1) | ||
| WEEKNUM | =WEEKNUM(date) | Week of the year | |
| DAYS | =DAYS(end_date, start_date) | =DAYS(DATE(2026,12,31),DATE(2026,10,21)) | 71 |
| EDATE | =EDATE(start_date, months) | =EDATE(DATE(2026,1,31),1) | 28-Feb-2026 |
| EOMONTH | =EOMONTH(start_date, months) | =EOMONTH(DATE(2026,2,10),0) | 28-Feb-2026 (last day of the month) |
| DATEDIF | =DATEDIF(start, end, unit) | see below | Gap between two dates |
| NETWORKDAYS | =NETWORKDAYS(start, end, [holidays]) | =NETWORKDAYS(DATE(2026,10,1),DATE(2026,10,31)) | 22 (Saturdays and Sundays excluded) |
| WORKDAY | =WORKDAY(start, days, [holidays]) | =WORKDAY(DATE(2026,10,21),10) | 4-Nov-2026 |
| YEARFRAC | =YEARFRAC(start, end) | Years as a fraction | |
| DATEVALUE | =DATEVALUE("21-10-2026") | Text to a date serial (depends on region settings) |
WEEKDAY return types: 1 (default) = Sunday is 1 to Saturday 7; 2 = Monday is 1 to Sunday 7; 3 = Monday is 0 to Sunday 6.
DATEDIF
A hidden function: it works, but it does not appear in the function list. Units, with start = 15 Jan 2000 and end = 21 Oct 2026:
| Unit | Gives | Result |
|---|---|---|
"Y" | Complete years | 26 |
"M" | Complete months | 321 |
"D" | Days | 9776 |
"YM" | Months after removing years | 9 |
"MD" | Days after removing months and years | 6 |
"YD" | Days after removing years | 279 |
Age: =DATEDIF(B2,TODAY(),"Y") gives the age in complete years from a date of birth in B2. Full age: =DATEDIF(B2,TODAY(),"Y")&" years, "&DATEDIF(B2,TODAY(),"YM")&" months".
Simple date arithmetic
- Days between two dates:
=B2-A2(format the result as General or Number). - Add 30 days:
=A2+30. Add 3 months:=EDATE(A2,3). Retirement date at 60:=EDATE(B2,60*12). - Last day of this month:
=EOMONTH(TODAY(),0). First day of this month:=EOMONTH(TODAY(),-1)+1. - Time worked:
=(End-Start)*24gives hours.
Keep practising
Notes are only half the work. Test yourself with questions and discuss doubts with other aspirants.
Prepared for exam revision from standard computer textbooks, Microsoft documentation and official sources. Features differ between Excel and Word versions (2016, 2019, 2021, 365) so check which version your exam follows. Found a mistake? Tell us.