<< Chapter < Page Chapter >> Page >
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

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.

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.

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.

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.

Questions & Answers

what is variations in raman spectra for nanomaterials
Jyoti Reply
I only see partial conversation and what's the question here!
Crow Reply
what about nanotechnology for water purification
RAW Reply
please someone correct me if I'm wrong but I think one can use nanoparticles, specially silver nanoparticles for water treatment.
Damian
yes that's correct
Professor
I think
Professor
what is the stm
Brian Reply
is there industrial application of fullrenes. What is the method to prepare fullrene on large scale.?
Rafiq
industrial application...? mmm I think on the medical side as drug carrier, but you should go deeper on your research, I may be wrong
Damian
How we are making nano material?
LITNING Reply
what is a peer
LITNING Reply
What is meant by 'nano scale'?
LITNING Reply
What is STMs full form?
LITNING
scanning tunneling microscope
Sahil
how nano science is used for hydrophobicity
Santosh
Do u think that Graphene and Fullrene fiber can be used to make Air Plane body structure the lightest and strongest. Rafiq
Rafiq
what is differents between GO and RGO?
Mahi
what is simplest way to understand the applications of nano robots used to detect the cancer affected cell of human body.? How this robot is carried to required site of body cell.? what will be the carrier material and how can be detected that correct delivery of drug is done Rafiq
Rafiq
what is Nano technology ?
Bob Reply
write examples of Nano molecule?
Bob
The nanotechnology is as new science, to scale nanometric
brayan
nanotechnology is the study, desing, synthesis, manipulation and application of materials and functional systems through control of matter at nanoscale
Damian
Is there any normative that regulates the use of silver nanoparticles?
Damian Reply
what king of growth are you checking .?
Renato
What fields keep nano created devices from performing or assimulating ? Magnetic fields ? Are do they assimilate ?
Stoney Reply
why we need to study biomolecules, molecular biology in nanotechnology?
Adin Reply
?
Kyle
yes I'm doing my masters in nanotechnology, we are being studying all these domains as well..
Adin
why?
Adin
what school?
Kyle
biomolecules are e building blocks of every organics and inorganic materials.
Joe
anyone know any internet site where one can find nanotechnology papers?
Damian Reply
research.net
kanaga
sciencedirect big data base
Ernesto
Introduction about quantum dots in nanotechnology
Praveena Reply
what does nano mean?
Anassong Reply
nano basically means 10^(-9). nanometer is a unit to measure length.
Bharti
do you think it's worthwhile in the long term to study the effects and possibilities of nanotechnology on viral treatment?
Damian Reply
absolutely yes
Daniel
Got questions? Join the online conversation and get instant answers!
Jobilize.com Reply

Get the best Algebra and trigonometry course in your pocket!





Source:  OpenStax, Engineering computation with spreadsheets. OpenStax CNX. Sep 30, 2011 Download for free at http://cnx.org/content/col11235/1.12
Google Play and the Google Play logo are trademarks of Google Inc.

Notification Switch

Would you like to follow the 'Engineering computation with spreadsheets' conversation and receive update notifications?

Ask