Skip to content Skip to navigation

Connexions

You are here: Home » Content » Date and Time Functions

Navigation

Recently Viewed

This feature requires Javascript to be enabled.
 

Date and Time Functions

Module by: Serhat Beyenir. E-mail the author

Summary: Basic date and time functions in Excel.

Date and Time Functions

To Excel, a date is simply a number. More precisely, a date is a “serial number” that represents the number of days since January 0, 1900. A serial number of 1 corresponds to January 1, 1900; a serial number of 2 corresponds to January 2, 1900, and so on. This system makes it possible to deal with dates in formulas. For example, you can create a formula to calculate the number of days between two dates.

Inputting Dates

You can enter a date directly as a serial number (if you know it), but more often you’ll enter a date using any of several recognized date formats. Excel automatically converts your entry into the corresponding date serial number (which it uses for calculations), and also applies the default date format to the cell so it displays as an actual date rather than a cryptic serial number. For example, if you need to enter January 25, 2010, you can simply enter the date by typing January 25, 2010 (or use any of several different date formats). Excel interprets your entry and stores the value 38741—the date serial number for that date. It also applies the default date format, so the cell contents may not appear exactly as you typed them. January 25, 2010

Inputting Times

When you need to work with time values, you simply extend Excel’s date serial number system to include decimals. In other words, Excel works with times by using fractional days. For example, the date serial number for January 25, 2010 is 38741. Noon (halfway through the day) is represented internally as 38741.5. 01/25/10 12:00 PM

Example 1

To calculate the difference between two times, you can subtract the earlier time from the later time to get the difference. For example, if cell A2 contains 5:30:00 and cell B2 contains 14:00:00, =B2-A2 returns 08:30:00 a difference of eight hours and 30 minutes. If the subtraction results in a negative value, however, it becomes an invalid time; Excel displays a series of pound signs (#######) because a time without a date has a date serial number of 0. A negative time results in a negative serial number, which is not permitted. This problem does not occur when you use a date along with the time.

Example 2

To convert decimal hours to a time, divide the decimal hours by 24. For example, if cell A1 contains 9.25 (representing hours) =A1/24 returns 09:15:00 nine hours, 15 minutes.

Example 3

To convert decimal minutes to a time, divide the decimal hours by 1,440 (the number of minutes in a day). For example, if cell A1 contains 500 (representing minutes) =A1/1440 returns 08:20:00 eight hours, 20 minutes.

Example 4

To convert decimal seconds to a time, divide the decimal hours by 86,400 (the number of seconds in a day). For example, if cell A1 contains 65,000 (representing seconds) =A1/86400 returns 18:03:20 18 hours, three minutes, and 20 seconds.

Formatting Dates and Times

Excel recognizes dates separated with a slash (/) or a hyphen (-) or dates entered using the month name. Times should be separated by colons (:). You have a great deal of flexibility in formatting cells that contain dates and times. For example, you can format the cell to display the date part only, the time part only, or both the date and time parts.

The NETWORKDAYS Function

The NETWORKDAYS function returns the number of whole workdays between the dates entered in cells A2 and B2, for example, =NETWORKDAYS(A2,B2)

The syntax for the NETWORKDAYS function is: NETWORKDAYS(start_date,end_date,holidays)

The NETWORKDAYS function’s arguments are as follows:

  • Start_date: A date that represents the start date.
  • End_date: A date that represents the end date.
  • Holidays: An optional range of one or more dates to exclude from the working calendar.

The WORKDAY Function

The WORKDAY returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. For example, if you start a project on January 25, 2010 and the project requires 10 working days to complete, the WORKDAY function can calculate the date you will finish the project. A working day consists of a weekday (Monday through Friday). =WORKDAY("1/25/2010",10) returns 7-Feb-06

The syntax for the WORKDAY function is: WORKDAY(start_date,days,holidays)

The NETWORKDAYS function’s arguments are as follows:

  • Start_date: A date that represents the start date.
  • Days: The number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.
  • Holidays: An optional range of one or more dates to exclude from the working calendar.

Content actions

Download module as:

PDF | More downloads ...

Add module to:

My Favorites (?)

'My Favorites' is a special kind of lens which you can use to bookmark modules and collections. 'My Favorites' can only be seen by you, and collections saved in 'My Favorites' can remember the last module you were on. You need an account to use 'My Favorites'.

| A lens I own (?)

Definition of a lens

Lenses

A lens is a custom view of the content in the repository. You can think of it as a fancy kind of list that will let you see content through the eyes of organizations and people you trust.

What is in a lens?

Lens makers point to materials (modules and collections), creating a guide that includes their own comments and descriptive tags about the content.

Who can create a lens?

Any individual member, a community, or a respected organization.

What are tags? tag icon

Tags are descriptors added by lens makers to help label content, attaching a vocabulary that is meaningful in the context of the lens.

| External bookmarks