How Are Time Values Stored In Excel?

Dates and times are two of the most common data types people work with in Excel, just they are also mayhap the most frustrating to work with, especially if you are new to Excel and still learning. This is considering Excel uses a serial number to correspond the appointment instead of a proper month, 24-hour interval, or twelvemonth, nevermind hours, minutes, or seconds. Information technology'southward made more than complicated by the fact that dates are as well days of the week, like Monday or Friday, even though Excel doesn't explicitly shop that data in the cells. Here is the definitive guide to working with dates and times in Excel…

How Excel Stores Dates

The source of most of the confusion effectually dates and times in Excel comes from the fashion that the program stores the information. You'd expect information technology to remember the month, the solar day, and the yr for dates, but that's not how it works…

Excel stores dates as a serial number that represents the number of days that have taken place since the kickoff of the twelvemonth 1900. This means that January 1, 1900 is really just a 1. January two, 1900 is 2. By the time we get all the way to the present decade, the numbers have gotten pretty big… September 10, 2013 is stored as 41527.

Importantly, any engagement earlier January 1, 1900 is non recognized equally a date in Excel. There are no "negative" date serial numbers on the number line.

It seems confusing, simply it makes it a lot easier to add, subtract, and count days. A week from September ten, 2013 (September 17, 2013), is just 41527 + 7 days, or 41534.

How Excel Stores Times

Excel stores times using the exact same series numbering format every bit with dates. Days start at midnight (12:00am or 0:00 hours). Since each hour is 1/24 of a day, information technology is represented as that decimal value: 0.041666…

That ways that ix:00am (09:00 hours) on September 10, 2013 will exist stored as 41527.375.

When a time is specified without a appointment, Excel stores it every bit if it occurred on January 0, 1900. In other words, 3:00pm (15:00 hours) is stored as 0.625. This can make doing math for time-only values (that have no engagement) challenging, since subtracting vi hours (6:00)  from 3:00am (03:00 hours) will become negative and count every bit an error: 0.125 – 0.25 = -0.125, which is displayed every bit #########.

Minutes and seconds in Excel work the aforementioned style as hours…

A minute is 1/60 of an hour, which is 1/24 of a day, or 1/1440 of a twenty-four hours in total, which calculates to 0.00069444…

A second is ane/threescore of an minute, which is 1/60 of an 60 minutes, which is 1/24 of a 24-hour interval, or 1/86400 of a day in total, which calculates to 0.00001157407…

Working with Dates and Times

DATE() and Time()

Series numbers aren't all that intuitive to use. Fortunately, Excel has a set of functions to brand it easier to find and utilise dates and times, starting with DATE and TIME. The syntax is as follows:

=Date(year,calendar month,twenty-four hour period)

=Time(hours,minutes,seconds)

For both functions, specify the year, month, and day, or hours, minutes, and seconds as numbers. For example, September 10, 2013 tin be entered as:

=Date(2013,9,10)

It will be stored equally 41527, which ways that it is technically storing 12:00am on September ten, 2013.

For times, 6:00pm (18:00 hours) can be entered every bit:

=Time(18,0,0)

It will be stored as 0.75, which ways that it is technically storing half-dozen:00pm on January 0, 1900.

If nosotros desire to represent a specific time and date, we tin can add together the two functions together. For example, 6:00pm (18:00 hours) on September 10, 2013 can be entered as:

=Date(2013,nine,10)+Fourth dimension(eighteen,0,0)

It will be summate as 41527.75, which means Excel is storing exactly the date we want…

Additional Date and Time Setting Functions

Excel has a few additional functions to brand declaring dates easier.

TODAY()

The TODAY office always returns the electric current engagement'south serial number. The TODAY function is just entered as:

=TODAY()

This article was written at 6:30pm (18:30 hours) on September 24, 2013, and the TODAY part calculated to 41541.  That ways that it is technically storing12:00am on September 24, 2013.

Now()

A similar function called Now always returns the current engagement and fourth dimension's series number. The At present office is just entered equally:

=NOW()

Once again,  at half-dozen:30pm (eighteen:30 hours) on September 24, 2013, the office calculated to 41541.77081333… NOW stores the exact time and date, down to the 2nd.

EDATE() and EOMONTH()

The EDATE function gives the appointment the specified number of months away from the input engagement. The EOMONTH function gives the date of the last day of the month. Information technology can do then for the electric current month or a number of months in the future or the past. The syntax for each is as follows:

=EDATE(start_date,          months)

=EOMONTH(start_date,          months)

The start_datetin can be whatsoever date-formatted cell reference or date serial number.

The months field can be any number, though but the integer value volition exist used (e.g. information technology treats 2.8 every bit 2).

The EDATE and EOMONTH functions strip the time value from the engagement. For instance, For instance, if cell A1 storesSeptember 10, 2013, we tin can become the value ii months ahead equally follows:

=EDATE(            A1          ,two)

Returns 12:00am (0:00 hours) on November ten, 2013, or 41588. This function works even though the months take different numbers of days (September and November accept 30, October has 31).

=EOMONTH(            A1          ,ii)

Returns12:00am (0:00 hours) onNovember 30, 2013, or41588. Again, this function works even though the months have different numbers of days.

WORKDAY()

Occasionally, it may exist useful to count ahead based on piece of work-days (Monday-Friday) instead of all vii days of the calendar week… For that, Excel has provided WORKDAY. The syntax for WORKDAY is as follows:

=WORKDAY(start_date,          days,          [holidays])

The start_date is as above.

The days input is the number of workdays ahead (or behind) of the present 24-hour interval you lot would similar to motion.

The [holidays] input is optional, but lets you disqualify specific days (like Thanksgiving or Christmas, for example), which might otherwise fall during the work calendar week. These are date series numbers provided in an array bounded past brackets: { }. To specify multiple holidays, the dates must be held in cells – it is non possible to put multiple DATE functions in an assortment.

For example, permit's detect the date 6 piece of work days before6:00pm (18:00 hours) onSeptember ten, 2013 (stored in cell A1). Mon, September 2d is Labor Day, and so let's include that equally a holiday:

=WORKDAY(            A1          ,-6,DATE(2013,nine,ii))

Returns12:00am (0:00 hours) onAugust xxx, 2013, or41516. (Notation that the office strips the time portion of the date.)

WORKDAY.INTL() (Excel 2010 and newer)

For newer versions of Excel (2010 and afterwards), there is another version of WORKDAY called WORKDAY.INTL. WORKDAY.INTL works just like WORKDAY, but it adds the ability to customize the definition of the "weekend". The syntax for WORKDAY.INTL is as follows:

=WORKDAY.INTL(start_date,          days,          [weekend],          [holidays])

The start_date, days, and [holidays] inputs work just like the normal WORKDAY function.

The [weekend] input has the following options:

Retrieving Dates in Excel

24-hour interval(), Calendar month(), and YEAR()

Now we know how define dates, but we still need to be able to piece of work with them. Serial numbers don't make it easy to extract months, years, and days, nevermind hours, minutes, and seconds. That's why Excel has specific functions for pulling out each of these values. For working with the calendar, there is DAY, Month, and Year. The syntax is simple:

=DAY(serial_number)

=MONTH(serial_number)

=YEAR(serial_number)

The serial_number in each can be any date-formatted prison cell reference. For example, if cell A1 storesSeptember 10, 2013, we can use each of the formulas in plow:

=DAY(            A1          )

Returns ten as a numeric value.

=Calendar month(            A1          )

Returns 9as a numeric value.

=YEAR(            A1          )

Returns 2013equally a numeric value.

We could have too given the straight serial number for September 10, 2013:

=DAY(41527)

Returns10 as a numeric value.

Retrieving Times in Excel

Hr(), Minute(), and 2d()

For times, the process is very similar. Excel has function to call back the hours, minutes, and seconds from a fourth dimension stamp, conveniently named HOUR, MINUTE, and SECOND. The syntax is identical:

=Hr(serial_number)

=Minute(serial_number)

=2d(serial_number)

Theserial_number in each tin be any time/date-formatted prison cell reference. For example, if A1 storessix:15:30pm (xviii:xv hours, 30 seconds) onSeptember 10, 2013, we tin use each of the formulas in plough:

=HOUR(            A1          )

Returns xviiias a numeric value.

=MINUTE(            A1          )

Returns xvas a numeric value.

=Second(            A1          )

Returns 30as a numeric value.

We could take also given the direct serial number for6:15:30pm (xviii:xv hours, 30 seconds) onSeptember 10, 2013:

=2d(41527.7607638889)

Returns 30 equally a numeric value.

Additional Engagement Retrieving Functions

WEEKDAY() and WEEKNUM()

Dates don't merely have month and twelvemonth information. They also encode indirect information…September 10, 2013 happens to likewise be a Tuesday. Excel has a few of functions to piece of work with the week aspect of dates: WEEKDAY and WEEKNUM. The syntax is as follows:

=WEEKDAY(serial_number,          [return_type])

=WEEKNUM(serial_number,          [return_type])

Theserial_number in each can be any date-formatted cell reference. Since [return_type] is optional, each role assumes that each week starts on Sun. If prison cell A1 storesSeptember 10, 2013 (a Tuesday), nosotros can use each of the formulas in turn:

=WEEKDAY(            A1          )

Returns iii, since Tuesday is the 3rd day of a calendar week that starts on Sunday.

=WEEKNUM(            A1          )

Returns 37, since September ten, 2013 is in the 37th week of 2013, when yous start counting weeks from Sunday.

The [return_type] allows you lot to specify a different default week organisation. You lot could let the calendar week start on Monday and run until Sunday, or Sat until Friday, for example. Excel is annoying, however, and makes the entry different for WEEKDAY and WEEKNUM. The total listing of options for WEEKDAY is equally follows:

Options 2 and 11 are functionally the same – the kickoff is merely there for backwards compatibility with before versions of Excel.

The full list of options for WEEKNUM is as follows:

Counting and Tracking Dates

Dates tin can exist added and subtracted like normal numbers considering they're stored as serial numbers. That lets you count the days betwixt two unlike dates. Sometimes, though, y'all need to count by a unlike metric.

NETWORKDAYS()

Above, we learned well-nigh WORKDAY, which lets you lot movement back and forth a set number of workdays, ignoring weekends and holidays. But what if you need to mensurate the number of workdays between ii dates? For that, Excel provides NETWORKDAYS. The formula syntax is as follows:

=NETWORKDAYS(start_date,          end_date,          [holidays])

The start_date and end_datecan be any date-formatted cell reference.

The[holidays] input is optional, just lets you disqualify specific days (similar Thanksgiving or Christmas, for case), which might otherwise fall during the piece of work calendar week. These are engagement serial numbers provided in an array divisional past brackets: { }. To specify multiple holidays, the dates must be held in cells – it is not possible to put multipleEngagement functions in an array.

For instance, if A1 contains6:00pm (18:00 hours) onSeptember 10, 2013 and B1 contains ix:00am (ix:00 hours) on December 2, 2013, nosotros can use NETWORKDAYS to find the number of workdays betwixt the two dates.

Let's exclude Columbus Mean solar day (Oct 14, 2013), Veterans 24-hour interval (November xi, 2013), and Thanksgiving Day (November 28, 2013) every bit holidays. To do so, nosotros have to store those dates in other cells. Let's put them in C1, C2, and C3:

=DATE(2013,10,14)

=DATE(2013,eleven,11)

=Engagement(2013,11,28)

Now, we can combine them in the function:

=NETWORKDAYS(            A1          ,            B1          ,            C1:C3          )

The role returns 57 as a numeric value.

NETWORKDAYS.INTL() (Excel 2010 and newer)

For newer versions of Excel (2010 and later), at that place is some other version of NETWORKDAYScalledNETWORKDAYS.INTL.NETWORKDAYS.INTL works just likeNETWORKDAYS, but it adds the power to customize the definition of the "weekend". The syntax forNETWORKDAYS.INTL is as follows:

=NETWORKDAYS.INTL(start_date,          end_date,          [weekend],          [holidays])

Thestart_date,end_date, and[holidays] inputs piece of work merely like the normalWORKDAY function.

The[weekend] input has the following options:

YEARFRAC()

Sometimes it's useful to measure how much fourth dimension has passed in years, simply subtracting the Yr office will only round down to the nearest full year. YEARFRAC takes two dates and provides the portion of the year between them. The syntax is as follows:

=YEARFRAC(start_date,          end_date,          [basis])

Thestart_date andend_datecan be any date-formatted prison cell reference.

The[basis] input is optional, only lets you specify the "rules" for measuring the difference. Most of the fourth dimension, yous'll want to use option 1, only here is the total listing:

For example, ifA1 containsSeptember x, 2013 andB1 containsDecember 2, 2013, nosotros tin use YEARFRACto find the decimal portion of a year between the two dates:

=YEARFRAC(            A1          ,            B1          ,one)

Returns0.227397260273973.

DATEDIF() (Undocumented Function)

The YEARFRAC office gives yous the difference between dates as a fraction of a twelvemonth, only sometimes you need more than command. At that place is a powerful hidden function in Excel called DATEDIF that tin can do much more than. It tin can tell you the number of years, months, or days between two dates. It can too track based on only partial inputs, ignoring years or months when calculating days. The syntax for DATEDIF is as follows:

=DATEDIF(start_date,          end_date,          unit of measurement)

Thestart_date andend_datecan exist any date-formatted cell reference.

Theunit of measurement input asks you lot to specify a cord that represents the type of output you desire. This is slightly cumbersome, since you must wrap the input in quotes (" ").

For example, ifA1 containsSeptember x, 2012 andB1 containsDec two, 2013, we can utiliseDATEDIFto find the number of full years between the two dates:

=DATEDIF(            A1          ,            B1          ,"Y")

Returns 1 as a numeric value.

Using the aforementioned start_date and end_date inputs, hither are the output possibilities for DATEDIF using differentunit of measurement parameters:

Every time, theunit must be put in quotes (e.g. "Y" or "MD").

Converting Dates and Times from Text

DATEVALUE() and TIMEVALUE()

All of the above functions piece of work perfectly with engagement-formatted serial numbers in Excel. Unfortunately, dates and times are often imported into worksheets every bit text. Near of the contrasted functions like Calendar month and Hr are reasonably intelligent nigh converting on the fly. Occasionally it'due south useful to build a date value through concatenation. The two functions Excel provides for this purpose are DATEVALUE and TIMEVALUE. The syntax for each is equally follows:

=DATEVALUE(date_text)

=TIMEVALUE(time_text)

The date_text and time_text have any text string that looks similar a engagement or time.

This is how DATEVALUE responds to variousdate_text inputs:

This is how TIMEVALUE responds to various time_text inputs:

Converting Dates and Times to Text

TEXT()

Getting information converted to dates and times is bang-up, but you may also need to get information technology back out. Sometimes, you need a special format. Other times, you lot need to look for a date in a text cord, and have to lucifer using string tools like FIND and SEARCH. In that location is i master function for converting dates and times to text strings in Excel, called TEXT. The syntax for TEXT is equally follows:

=TEXT(value,          format_text)

The valuetin exist any engagement or time-formatted cell reference.

The format_text input has a large number of options, summarized here:

The outputs can be combined with simple formatting characters to produce standard date formats. Using the appointment 5:07:03am (05:07 hours, 3 seconds) on September 10, 2013, hither are examples of possible outputs:

A Common Problem

One issue people frequently meet is that Excel occasionally misinterprets text fields as dates. An example is hither:

Exist careful when entering dates, especially if you are importing from other data sources, to make certain that your "Jan-13" is existence stored as Jan ane, 2013 and not January 13, 2013!

Go the latest Excel tips and tricks by joining the newsletter!

Andrew Roberts has been solving concern problems with Microsoft Excel for over a decade. Excel Tactics is defended to helping y'all chief it.

Join the newsletter to stay on top of the latest articles. Sign up and you lot'll get a costless guide with 10 time-saving keyboard shortcuts!

Other posts in this series...

Source: https://www.exceltactics.com/definitive-guide-using-dates-times-excel/

Posted by: dominguesmazing.blogspot.com

Share this post