Category Archives: Time and Date

Convert Decimal Hours to Time in Excel

Converting a decimal time value to minutes and hours is easily done in Excel. That is, if the time value is less than 24 hours.
Change Decimal to Time in Three Steps

Decimal to Time Conversion

Here are three simple steps to convert decimal to time.

  1. Enter a decimal value into a cell
  2. Divide by 24
  3. Change cell formatting to Time (h:mm)

To change the cell format use keyboard shortcut Ctrl+1 to bring up the Format Cells dialog box, select the Number tab, click Time, then select the h:ss format as shown below.

Change Time Format

If  you have a lot of conversions then one column for the decimal values and another column for a formula dividing by 24 will suffice. Just change the formatting for the second column. The example above shows three columns for illustrative purposes and is not very practical.

Obviously a conversion can be done in one cell, if you:

  • Enter a formula into a cell with a decimal value divided by 24 (=0.61/24)
  • Format that cell to h:mm

And remember, this only works properly for decimal values less than 24 hours.

The WEEKDAY Function in Excel

Excel WEEKDAY functionExcel 2010 has new return_type arguments for the WEEKDAY Function. How useful they are is debatable, but you never know when you might come across a particular need.

Excel Weekday Function

The Excel WEEKDAY Function in has two arguments: serial_number and return_type. The serial_number argument is simply a number that represents a date. The return_type argument is optional, and assumed to be 1 if omitted.

Versions prior to Excel 2010 have only the first three return_type arguments: 1,2 3.  The new arguments are 11-17, representing differing combinations for the starting day of the week.

The Excel WEEKDAY Function returns an integer value 1-7, based upon the day of the week, with the exception of return_type 3 that returns 0-6.

The Excel WEEKDAY Function Arguments dialog box doesn’t show the new arguments.

WEEKDAY Function Arguments Dialog Box

However in Excel 2010, when you enter the WEEKDAY Function into a cell, they are shown as you enter the second argument.

Excel WEEKDAY function

Weekly Reporting and the Excel WEEKDAY Function

I’ve only used the WEEKDAY Function with regards to reporting data, and then only sporadically.

Companies normally have weekly reporting requirements. However, while every week has seven days, there are two important distinctions. First is the start and end days of each week. Some companies use a Sunday-Saturday week, others a Monday-Sunday. These two are the most common, although I came across one company that had a Saturday-Friday reporting week.

The second distinction in weekly reporting is what day is used to represent the entire week. In the USA it’s common to use a week-ending date for weekly reports, while in Europe a week-commencing date is used quite frequently.

I won’t go into specific uses of the WEEKDAY Function for reporting in this post, but the two distinctions mentioned for weekly reporting make it a handy function to know.

Date and Time Calculation in Excel

The unit of time in Excel is the Day. By using the NOW Function to show both date and time, you can see the underlying serial number by changing the cell format to General.

This serial number is composed of two parts: the date part is the integer and the time part is the decimal.

Time calculator Excel

The Date serial number is an integer value based upon a Date System, either Windows or Mac. The Time serial number is represented as a decimal fraction because time is considered a portion of a day.

The Date and Time Calculator (Excel)

In the table below I entered a date in cell B2, a time in C2, and a formula in D2 that adds the two together. Excel automatically formatted this cell using a m/d/yyyy hh:mm format. I changed the row 3 cell formatting to General so you can see the serial number values. This is the function for time calculator in Excel.

Date Time Values in Excel

You can clearly see that Date is a integer value, Time is a decimal fraction, and the Date/Time format has both together in one number.

Date and Time Calculations

In Excel, date serial number 1 is Jan 1, 1900 for the Windows Date System. The date serial number 40364 represents July 5, 2010 because it’s 40,363 days after Jan 1, 1900. These date serial numbers are used in calculations by Excel.

Time is a decimal value from zero (0) to 0.99999999, representing times from 0:00:00 (midnight) to 23:59:59 (11:59:59 pm).

Think about time values as the number of seconds past 12:00 AM divided by the number of seconds in a day, 86,400. Obviously this can be simplified if using only hours or minutes. For example, the time value for 6:00 AM is 0.25, which is 6hrs divided by 24hrs.

The time shown above (7:12:30 AM) has a decimal value of 0.300347222. The calculation is:

  • 7 hours = 7 hrs x 60 min/hr x 60 sec/min = 25,200 seconds
  • 12 minutes = 12 min x 60 sec/min = 720 seconds
  • Total seconds = 25,200 + 720 + 30 = 25,950
  • Decimal value = 25,950 / 86,400 = 0.300347222

Change the cell formatting to Time and you will see 7:12:30 AM (or your local Time setting format).

Tip: When subtracting two different times not in the same day, the integer portion of the serial number must be used or the calculation will be invalid.

Convert a Text String Date to Date Serial Number

In previous posts I’ve been going through some formulas designed to extract and convert a text string Date, to a Date serial number.

Given the text string Date below in cell A2, what I normally do is enter a single formula, then change the cell formatting.

Convert Text Date to Serial Date Formula Unformatted

The formula above in cell B2 is:

= – – (MID(A2,5,6) & “, ” & RIGHT(A2,4))

Which leaves a numeric date serial number.

I then change the formatting by using the keyboard shortcut Ctrl+1 to bring up the Format Cells dialog box, click the Number tab, select Date (leaving the default short date format), and then click OK.

Format Cells Dialog Box

Oh, and then copy the formula down by selecting cell B2 and double clicking the fill handle.

Convert Text Date to Serial Date Formula

There are a number of things in this formula: double negation, MID Function, Ampersand Operator, and RIGHT Function that combine easily, once you know how the individual parts work.

The VALUE Function in Excel – Converting Text Dates

The VALUE Function formula in cell G2 has converted the text string Date from cell E2, into a date serial number.

=VALUE(E2)

Until recently, I always used the VALUE Function to do this conversion. It’s something that’s well documented in the Function Arguments.

Excel Value Function

Excel VALUE Function Argument Dialog Box

Alternative Formulas to Convert Text Dates

However, all that’s required to change this text string Date into a numeric value (date serial number) is to perform a simple mathematical operation that does not change the value. Excel will then do the conversion for you.

The following formulas can be used as a substitute for the Excel value function =VALUE(E2), where E2 is the text string Date:

=E2 + 0

=E2 * 1

=E2 / 1

= – – E2

Each formula takes advantage of a simple mathematical operation: addition, multiplication, division, and double negation. Excel converts the text string Date into a Date serial number, in this case 40359, and then applies the mathematical operation(s). None of which will change the numeric value.

Quick Conversion of Dates in a Range

To do a quick conversion on an entire range, John Walkenback, in his book Excel 2007 Formulas has a neat trick.

  • Copy an empty cell
  • Click the range with values
  • Paste Special → In the dialog box select Add
  • Then click OK

Excel converts text string Dates to serial numbers, then adds a zero and you end up with the date serial number. Pretty slick.

Formatting a Date Serial Number

In all of these conversions you end up with a date serial number that can be changed to a Date by modifying the cell formatting. I use the keyboard shortcut Ctrl+1, select the Number tab and click Date.

In my next post I’ll use one formula to convert the text string date into a real numeric date.

Extract a Date from Text in Excel

It’s not uncommon for database programs to export data in a text format. I downloaded some data from a web browser, and ended up with Dates that Excel didn’t recognize as Dates, but rather a Text string.

In a previous post I used the Text to Columns feature to extract and convert the Month and Day into a Date Format. Excel used the computer’s System Date to supply the Year. Since all the data happened to be for 2010 there was no problem.

Had there been data from any year other than 2010, the Month and Day wouldn’t have been sufficient to give the correct Date. Hence a formula is a better way to extract the Date, in this instance.

It was a simple matter for me to quickly put together a formula to do just that, but there are several parts to the formula, none of which I’ve covered in this blog. I really don’ t want this post to drag on to infinity and beyond 🙂 so I’m going to break the formula down in the next several posts and cover the following:

  • MID Function
  • RIGHT Function
  • CONCATENATE Function and Ampersand
  • TYPE Function
  • VALUE Function and Double Negation

So I guess this is the first part of a series to Extract a Date from Text in Excel.

Generate a List of Dates in Excel

There are times when generating a list of dates comes in handy. Perhaps you need to log something every day for an entire year.

Or maybe you’re tracking stock prices and want to skip the weekend and only use weekday dates. How about a list of dates for each week of the year? Or just month ending days?

Dates for all of these examples can be created with Excel by filling a series.

Here is a bullet list of steps to follow.

  • Enter a starting date into a cell
  • Right click the cell’s fill handle, drag down and back up, and release the mouse handle
  • Click Series from the pop-up menu
    • This brings up the Series dialog box, or you can use Edit » Fill » Series… and skip these last two steps.
  • Select Columns for Series in
  • Make sure Date is selected for Type
  • Make sure Day is selected for Date Unit
  • Leave Step value as 1 for days
  • Enter an ending date for Stop value
  • Click OK

This will give you a series of days between the starting date you entered in the worksheet, and the Stop value date. Changing the Date unit to Weekday will exclude all the weekend dates.

If you change the Step value to 7 you will get weekly dates. If you want a Monday date each week, then pick a Monday date for your start date. If you want Friday dates, pick a Friday start date.

Leaving the Step value to 1 and changing the Date Unit to Month will generate, you guessed it, a series of monthly dates. Your starting date will determine which day is being replicated. Should your starting date contain the last day of the month, Excel is smart enough to generate a list for the last day of each month.

If the date series is not what was expected, it can be undone by using the keyboard shortcut Ctrl+z. I used this often as Rows is the default for Series in and I kept forgetting to change it to Columns about every fourth time.

Generating a list of dates in Excel works in versions 2000, 2002, 2003, 2007, 2010, and Excel for Mac 2008.

Microsoft Excel Date Systems for Windows and Mac

Microsoft Excel has two different date systems that you will probably never think about because they exist in two different worlds: Windows or Mac. However, if you are sharing Excel files between Windows and Mac computers the potential exists for a great deal of confusion around dates.

What is a Date System?

Dates are represented in Excel by a numbering system. Days are assigned whole numbers, known as serial numbers, because the basic unit of time in Excel is the day. Each date system has a base date, or starting date, represented by the number 1.

The Windows date system has a starting date of January 1, 1900 that is given the serial number 1. The next day, January 2, is serial number 2, and so on.

The starting date in the Macintosh date system is January 2, 1904, which is given the serial number 1. The next day, January 3, is represented by serial number 2, etc.

Default Date Systems for Windows and Mac

The default workbook setting in Microsoft Excel 2010 is the 1900 date system. In the Excel Options dialogue box you are given the option to Use the 1904 system, should you so desire.

Excel Windows Date System

As you can see below, Microsoft Excel for Mac 2008 has the 1904 date system as the default workbook setting. By un-checking this option the workbook will use the 1900 date system.

Excel Date System Mac 2008

Problems Mixing Excel Date Systems

A major problem can occur if you copy or use a formula reference to dates from a workbook with one date system to a workbook with another date system. Since the two date systems use different base dates they show up as different formatted dates.

Some explanation here would be helpful.

Well known to Excel geeks, the serial number 40,000 is formatted as 7/6/2009 in the Windows 1900 date system. However, the same 40,000 serial number is formatted as 7/7/2013 in the 1904 date system. Same serial number, different formatted date. Note: I’m using the date convention m/d/yyyy.

If I copy the date 1/15/2010 from a Mac to a Windows version of Excel the formatted date will appear as 1/14/2006, yet the underlying serial number is 38731 in both versions. This happens because the two date systems have a difference of 1,462 days between the start dates.

In the picture below there are two worksheets. The one on the left, Date Test Windows.xlsx, was created in Excel 2007 (Windows) and the one on the right, Date Date Test Mac.xlsx, was created using Excel for Mac 2008.

In column A of both worksheets I entered a series of dates from 1/1/2010 to 1/15/2010. The last row’s cell formatting has been changed to “General” so you can see the underlying serial date for 1/15/2010.

Date System Comparison Windows vs Mac

In column B of both worksheets I used a formula to reference column A of the other worksheet so you can how the same serial number is represented differently by each date system. For reference: on the Date Test Window worksheet, the formula in cell B16 is  ='[Date Test Mac.xlsx]Sheet1′!A16′.

Working with Windows and Mac Versions of Excel

One time a colleague of mine started a data collection worksheet using Excel for Mac and one of the data fields was the date. After data had been collected for over a week I realized the workbook was using the 1904 date system.

Knowing the file would be used exclusively on Windows computers I endeavored to change the date system setting and realized there would have to be some conversion of the existing data. In a different column I simply entered a formula to subtract 1,462 from the existing dates, copied, and then pasted as values. Next I un-checked the box for the 1904 date system and used the new date column.

My recommendation is to be aware if the Excel file you are using has come from a Mac and if so, check out which date system is being used. If the file will be used primarily on Windows computers then it’s best to use the 1900 date system.

Be careful when changing the settings for the date system because the dates will change for any current data in the workbook. If you have dates you need to convert from one system to another, adding or subtracting 1,462 to the dates can give you a quick solution for what I call date dementia.

Related Posts Plugin for WordPress, Blogger...