1.

Date & Time Functions

To enter a date in Excel, use the "/" or "-" characters. To enter a time,
use the ":" (colon). You can also enter a date and a time in one cell.

img1

Note: Dates are in US Format. Months first, Days second. This type of
format depends on yourwindows regional settings. Learn more about Date
and Time formats.

Year, Month, Day

To get the year of a date, use the YEAR function.

img2

Note: use the MONTH and DAY function to get the month and day of a
date.

1
Date Function

1. To add a number of days to a date, use the following simple formula.

img3

2. To add a number of years, months and/or days, use the DATE function.

img4

Note: the DATE function accepts three arguments: year, month and day.
Excel knows that 6 + 2 = 8 = August has 31 days and rolls over to the next
month (23 August + 9 days = 1 September).

Current Date & Time

To get the current date and time, use the NOW function.

img5

2
Note: use the TODAY function to get the current date only. Use NOW()-
TODAY() to get the current time only (and apply a Time format).

Hour, Min, Sec

To return the hour, use the HOUR function.

img6

Note: use the MINUTE and SECOND function to return the minute and
second.

Time Function

To add a number of hours, minutes and/or seconds, use the TIME function.

img7

Note: Excel adds 2 hours, 10 + 1 = 11 minutes and 70 - 60 = 10 seconds.

3
2. DateDif

To get the number of days, weeks or years between two dates in Excel,
use the DATEDIF function. The DATEDIF function has three arguments.

1. Fill in "d" for the third argument to get the number of days between two
dates.

img8

Note: =A2-A1 produces the exact same result!

2. Fill in "m" for the third argument to get the number of months between
two dates.

img9

3. Fill in "y" for the third argument to get the number of years between two
dates.

4
img10

4. Fill in "yd" for the third argument to ignore years and get the number of
days between two dates.

img11

5. Fill in "md" for the third argument to ignore months and get the number
of days between two dates.

img12

5
6. Fill in "ym" for the third argument to ignore years and get the number of
months between two dates.

img13

Important note: the DATEDIF function returns the number

of complete days, months or years. This may give unexpected results when
the day/month number of the second date is lower than the day/month
number of the first date. See the example below.

img14

The difference is 6 years. Almost 7 years! Use the following formula to

return 7 years.

img15

6
3. Weekdays

Weekday Function | Networkdays function | Workday function

Learn how to get the day of the week of a date in Excel and how to get
the number of weekdays/working days between two dates.

Weekday Function

1. The WEEKDAY function in Excel returns a number from 1 (Sunday) to 7

(Saturday) representing the day of the week of a date. Apparently,
12/16/2013 falls on a Monday.

img16

2. You can also use the TEXT function to display the day of the week.

img17

3. Create a custom date format (dddd) to display the day of the week.

7
img18

Networkdays Function

1. The NETWORKDAYS function returns the number of weekdays

(weekends excluded) between two dates.

img19

2. If you supply a list of holidays, the NETWORKDAYS function returns the

number of workdays (weekends and holidays excluded) between two
dates.

img20

The calendar below helps you understand the NETWORKDAYS function.

8
img21

3. Dates are stored as numbers in Excel and count the number of days
since January 0, 1900. Instead of supplying a list, supply an array constant
of the numbers that represent these dates. To achieve this, select E1:E2 in
the formula and press F9.

img22

Workday Function

The WORKDAY function is (almost) the opposite of the NETWORKDAYS

function. It returns the date before or after a specified number of weekdays
(weekends excluded).

9
img23

Note: the WORKDAY function returns the serial number of the date. Apply
a Date format to display the date.

The calendar below helps you understand the WORKDAY function.

img24

Again, if you supply a list of holidays, the WORKDAY function returns the
date before or after a specified number of workdays (weekends and
holidays excluded).

10
4. Days until Birthday

To calculate the number of days until your birthday in Excel, execute the
following steps.

1. Select a cell and enter your date of birth.

2. Select the cell next to it and enter the TODAY function to return today's
date.

img25

3. The most difficult part in order to get the number of days until your
birthday is to find your next birthday. The formula below does the trick.

img26

Explanation: The DATE function accepts three arguments: year, month and
day. We used the DATEDIF function to find the number of complete years
("y") between Date of Birth and Today. DATEDIF(A2,B2,"y") equals 32. If
32 complete years have passed since your date of birth (in other words,
you have already celebrated your 32st birthday), your next birthday will be
32 + 1 = 33 years after your date of birth.

11
4. Next, we use the DATEDIF function to find the number of days ("d")
between Today and Next Birthday.

img27

6. Last Day of the Month

To get the date of the last day of the month in Excel, use the EOMONTH
(End of Month) function.

1. For example, get the date of the last day of the current month.

img32

Note: the EOMONTH function returns the serial number of the date. Apply
a Date format to display the date.

2. For example, get the date of the last day of the next month.

12
img33

3. For example, get the date of the last day of the current month - 8 months
= 6 - 8 = -2 = October (-2+12=10), 2011!

img34

8. Quarter

An easy formula that returns the quarter for a given date. There's no built-
in function in Excel that can do this.

1. Enter the formula shown below.

img39

Explanation: ROUNDUP(x,0) always rounds x up to the nearest integer.

The MONTH function returns the month number of a date. In this example,

13
the formula reduces to =ROUNDUP(5/3,0), =ROUNDUP(1.666667,0), 2.
May is in Quarter 2.

2. Let's see if this formula works for all months.

img40

Explanation: now it's not difficult to see that the first three values (months)
in column B are rounded up to 1 (Quarter 1), the next three values
(months) in column B are rounded up to 2 (Quarter 2), etc.

9. Day of the Year

An easy formula that returns the day of the year for a given date. There's
no built-in function in Excel that can do this.

1. Enter the formula shown below.

img41

14
Explanation: Dates and times are stored as numbers in Excel and count the
number of days since January 0, 1900. June 23, 2012 is the same as
41083. The DATE function accepts three arguments: year, month and day.
DATE(YEAR(A1),1,1) or 1-jan-2012 is the same as 40909. Subtracting
these numbers (41083 - 40909 = 174) and adding 1 gives the day of the
year.

15