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

Is there any normative that regulates the use of silver nanoparticles?
Damian Reply
what king of growth are you checking .?
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
yes I'm doing my masters in nanotechnology, we are being studying all these domains as well..
what school?
biomolecules are e building blocks of every organics and inorganic materials.
anyone know any internet site where one can find nanotechnology papers?
Damian Reply
sciencedirect big data base
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.
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
how to know photocatalytic properties of tio2 nanoparticles...what to do now
Akash Reply
it is a goid question and i want to know the answer as well
characteristics of micro business
for teaching engĺish at school how nano technology help us
Do somebody tell me a best nano engineering book for beginners?
s. Reply
there is no specific books for beginners but there is book called principle of nanotechnology
what is fullerene does it is used to make bukky balls
Devang Reply
are you nano engineer ?
fullerene is a bucky ball aka Carbon 60 molecule. It was name by the architect Fuller. He design the geodesic dome. it resembles a soccer ball.
what is the actual application of fullerenes nowadays?
That is a great question Damian. best way to answer that question is to Google it. there are hundreds of applications for buck minister fullerenes, from medical to aerospace. you can also find plenty of research papers that will give you great detail on the potential applications of fullerenes.
what is the Synthesis, properties,and applications of carbon nano chemistry
Abhijith Reply
Mostly, they use nano carbon for electronics and for materials to be strengthened.
is Bucky paper clear?
carbon nanotubes has various application in fuel cells membrane, current research on cancer drug,and in electronics MEMS and NEMS etc
so some one know about replacing silicon atom with phosphorous in semiconductors device?
s. Reply
Yeah, it is a pain to say the least. You basically have to heat the substarte up to around 1000 degrees celcius then pass phosphene gas over top of it, which is explosive and toxic by the way, under very low pressure.
Do you know which machine is used to that process?
how to fabricate graphene ink ?
for screen printed electrodes ?
What is lattice structure?
s. Reply
of graphene you mean?
or in general
in general
Graphene has a hexagonal structure
On having this app for quite a bit time, Haven't realised there's a chat room in it.
what is biological synthesis of nanoparticles
Sanket Reply
how did you get the value of 2000N.What calculations are needed to arrive at it
Smarajit Reply
Privacy Information Security Software Version 1.1a
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?