<< 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 do you understand by Ceteris Paribus?
Gabriel Reply
explain the uses of microeconomics
Nikita Reply
uses of microeconomics
Adam Smith's definition of economics
Sylvia Reply
what is economic deficit
this is a situation whereby a nation's outcome or available resources are not enough to the people thereby causing scarcity
prices of Quality demanded is equal to Quality supplied
it's quantity demand and quantity supplied that's called equilibrium
they deal With prices
define the elasticity
explain different types of elasticity
oops 😬 you are right you talk about quality I tell about quantity
elasticity is the measurement of the percentage change of one economic variable in response to a change in another
Cross Elasticity of Demand (XED) Income Elasticity of Demand (YED) Price Elasticity of Supply (PES)
anything else?
I need to know everything about theory of consumer behavior
How does one analyze a market where both demand and supply shift?
Gabriel Reply
That's equilibrium market
but an equlibrum can appear twice on the same market... both in Movement along the Demand/supply curve of shift in the Curve
I Mean on the same curve..
how can consumer surplus be calculated
How can we analyze the effect on demand or supply if multiple factors are changing at the same time—say price rises and income falls? 
Gabriel Reply
because of fall of income, less will be demanded and much will be supply as a result of price rises. Rise in price always motivate new supplier to enter into the system. But it only possible in the short run
yeah.. I think Ceteris Paribus is applied in this case
that is the law of Demand is Inversely related to the law of Supply... so that mean a positive change in demand may produce a negative return to supply I think.
what are the difference between Wants and Needs
Gabriel Reply
When the price is above the equilibrium, explain how market forces move the market price to equilibrium. Do the same when the price is below the equilibrium.
economic problems
yeah please Explain
I don't know this is my question
no it was a mistake...😂😂 can you explain how Wants and needs differs 😌
wants is what human desire but might not need them, human want are mostly articles of ostentatious while need is what human must get to live e.g inferior goods
what's equilibrium price
equilibrium prices is a situation whereby the price of goods supplied equates to the demand
this whereby the prices of quality demanded is equivalent to quality demanded
wants are numerous desire man that man can do without if not purchased e.g. cosmetic while need are desires that you cannot do without e.g. food
equilibrium price is that level of output were quantity demanded is equal to quantity supplied
what are the importance of studying economics
Bherla Reply
To know if the country is growing or not through the country's GDP
to manage our resources
compare base years GDP and the current years GDP
To tell whether a country is growing there are many factors to be considered not necessarily only the GDP due to weaknesses of GDP approach
What is the law of demand
Yaw Reply
price increase demand decrease...price decrease demand increase
ıf the price increase the demand decrease and if the demand increase the price decrease
all other things being equal, an increase in demand causes a decrease in supply and vice versa
how is the economy of usa now
What is demand
jude Reply
Demand is the quantity of goods and services a consumer is willing and able to purchase at various prices over a given period of time.
Okay congratulations I'll join you guys later .
demand is the quantity and quality of goods and services a consumer is willingly and able to purchase at a particular price over a given period of time.
calculate elasticity of income exercises
If potatoes cost Jane $1 per kilogram and she has $5 that could possibly spend on potatoes or other items. If she feels that the first kilogram of potatoes is worth $1.50, the second kilogram is worth$1.14, the third is worth $1.05 and subsequent kilograms are worth $0.30, how many kilograms of potatoes will she purchase? What if she only had $2 to spend?
Susan Reply
cause of poverty in urban
DAVY Reply
QI: (A) Asume the following cost data are for a purely competitive producer: At a product price Of $56. will this firm produce in the short run? Why Why not? If it is preferable to produce, what will be the profit-maximizing Or loss-minimizing Output? Explain. What economic profit or loss will the
Falak Reply
supply function Qs=0+20P price of bread 30
Maricar Reply
explain the various types of cost curve
Ruth Reply
Short-run average fixed cost (SRAFC) Short-run average total cost (SRAC or SRATC) Short-run average variable cost (AVC or SRAVC) Short-run fixed cost (FC or SRFC) Short-run marginal cost (SRMC) Short-run total cost (SRTC)
Got questions? Join the online conversation and get instant answers!
Jobilize.com Reply

Get Jobilize Job Search Mobile App in your pocket Now!

Get it on Google Play

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?