I use the Woopra desktop app to view live visitors to this WordPress blog. Visitors will see a Woopra window lurking about when I’m logged into my (free) Woopra account. One option is to initiate a chat session with me.
My first chat happened the other day. The chat notice startled me, but I quickly recovered and was able to interact with a visitor.
(Bold and color are my additions for clarity)
Visitor #13357:
08:53:19. HI
You:
08:53:28. Howdy
Visitor #13357:
08:54:22. I WANT TO CONVERT 01:05 = 65 MIN WHAT I DO
08:55:42. IN EXCEL
You:
08:56:58. In cell A1 type 1:05, and in cell B1 enter the formula =A1*24*60 then change the cell format to General and you’ll see the 65.
Visitor #13357:
08:57:42. I NEED IN THIS FORMAT
08:57:46. 65 MIN
You:
08:58:41. Change the formula in B1 to =A1*24*60 & ” MIN”
Visitor #13357:
09:00:21. MY TIME FORMAT IN 01:01 AND IT SHOWS 60.9999999999999 MIN NOT 61 MIN
You:
09:03:04. Try this =TEXT(A1*24*60,”#”) & ” MIN”
Visitor #13357:
09:04:38. THANKS ITS WORKING
09:04:44. THANKS A LOT
You:
09:04:49. Glad to help.
The strange thing I just noticed is that some of my replies have periods at the end. In a chat session no less!
In a previous post I went on and on about how you can calculate hours between two different dates and times, but Excel does this for you automatically. I’ll use reader comment as my example.
A-02.02.2011 10:00
B-05.02.2011 17:00
Please help me to find B-A in hh:mm
For clarity I will assume that the date format is m/d/yyyy, which is what I use here in the US.
For the A value type in 2/2/2011 10:00 and for the B value type in 5/2/2011 17:00 then in another cell subtract A from B. In the (B-A) cell enter the custom time format [h]:mm to show the result in hh:mm format.
To enter the custom time format:
Open the Format Cells dialog box (CTRL+1)
Select the Number tab
Click Custom
In the Type box enter [h]:mm
Click OK
When you surround the h with a square bracket [] Excel will show hours as elapsed time. Without the square brackets the hours will only show values 0-23.
Should you want to include seconds, the custom time format would be [h]:mm:ss. To illustrate this I’ll change the B value to 5/2/2011 17:15:44 and the resulting formula will show 2143:15:44.
Given the Date in this spreadsheet, a simple formula using the DATE Function will return the Day of Year. Go ahead and type a Date value in cell A2 to see how the formula works, the spreadsheet is embedded from my SkyDrive. (Date format is US, “m/d/yyyy”)
Click here if you can’t see the embedded worksheet.
The Day of Year Formula
The formula in cell B2 is:
= A2-DATE(YEAR(A2),1,0)
The DATE Function takes the Year from the Date you type in cell A2, and uses the zero (0) day of month 1 (January), which is the last day of the previous month. (I explained this in an earlier post).
The formula takes the Date you enter, and subtracts December 31st of the previous year to come up with the Day of Year.
You can copy the formula by double clicking cell B2, use Ctrl+A to select all the contents, and Ctrl+C to copy to the clipboard.
[UPDATE] Problems with SkyDrive
Apparently there are some problems with using the embedded worksheet on this web page. I added a public link to the file on Windows SkyDrive, but that seems to have some issues, depending on whether you use a Mac or Windows computer.
If you have problems with the embedded worksheet on this webpage, or with the web version on SkyDrive let me know in the comments what problem(s) you’re having, what hardware (Windows computer, Mac computer, iOS device – iPad, or other device) and what web browser (Firefox, Chrome, Safari, Opera, IE) you’re using. Thanks so much.
The DATEDIF Function comes in handy when you want to know how many years, months, and days there are between two dates. And even though it may be slightly flawed, it still beats the alternatives.
The following spreadsheet works well for birthdays or anniversaries. The DATEDIF Function is being used to generate the Years, Months, and Days.
The End Date column has the formula =TODAY().
The Years heading in cell D2 has the custom format ;;;”Years” which shows the text “Years” in the spreadsheet cell while hiding the underlying value “y” that you can see in the formula bar.
The headings for Years, Months, and Days all have a custom format. The Underlying Value is what’s important for the next section.
Using the DATEDIF Function
The DATEDIF function is present in all current versions of Excel, but is only documented in Excel 2000. It was provided for compatibility with Lotus 1-2-3.
The Syntax is:
DATEDIF(start_date, end_date, unit)
Start Date and End Date are self-explanatory, although the end date must come after start date. The Unit is the type of information you want to return.
The formula in cell D2 is =DATEDIF($B2,$C2,D$1) where $B2 is the Start Date, $C2 is the End Date, and D$1 is the Unit. This gives you =DATEDIF(“10/2/1987”, “1/11/2011”, “y”) and returns 23 years.
In the Months column I’m using the unit argument “ym” to return the number of months. (That’s the value in cell D1 you can’t see in the spreadsheet). The unit argument is “md” for the Days column. The formulas in row 2 are:
Years =DATEDIF($B2,$C2,D$1) = DATEDIF(“10/2/1987″,”1/11/2011″,”y”)
Days =DATEDIF($B2,$C2,F$1) = DATEDIF(“9/27/1975″,”1/11/2011″,”md”)
A Problem with DATEDIF Function
I used a column of Start Dates for the past year and happened to notice a pattern with the Days column. Every month in the DATEDIF Function seems to have 31 days.
The 31 Day Cycle
The first pair of dates are the 11th and 12th of 2010. The first date is 1 year, zero months and zero days from 1/11/2011. The second date is zero years, 11 months and 30 days from 1/11/2011.
The green pairs show the Days cycle from zero to 30 days, which means a 31 day cycle.
Missing Days
Now look at the red pairs of data. These are the only non-sequential Days returned by the DATEDIF for the past year.
The first pair has start dates of 2/28/2010 and 3/1/2010. The first date is zero years, 10 months and 14 days from 1/11/2011. The second date is zero years, 10 months, and 10 days from 1/11/2011.
There are 3 days missing from this one day sequential difference.
The rest of the red pairs of data skip one day, which is the day after a month with less than 31 days.
For the spreadsheet I’ve put together this DATEDIF anomaly is not going to make me lose any sleep. Missing a day or two in 10 to 50 years is not very bothersome.
Download the Spreadsheet
Here’s the spreadsheet, should you want to download it.
I’ve a simple formula for calculating the Xth Weekday of ANY month. It takes four inputs: Year, Month, Week, and Day. And requires a couple of lookup tables for data validation, one with special formatting.
The History
It all started when my wife mentioned there were a couple of meetings she had to schedule at work, one on the second Tuesday of every month and the other on the third Tuesday of every month. That got me to thinking about how to create a formula in Excel to calculate those dates.
After getting a partial solution, other things took precedence and it slipped from my mind until Chandoo posted a homework article on how to figure out what date Thanksgiving falls on each year, the 4th Thursday of November, in the USA.
His solution and all the variations provided in the comments (I posted one too) was focused and specific to one day (Thursday) of one week (the 4th) in one month (November). Albeit a link in that article showed another formula solution to Canada’s Thanksgiving day that takes place on the 2nd Monday of October.
This served to spur me on and finish what I started. I hope you like it.
The Inputs
Input for the Year in cell A2 is done manually. Just type in a year.
Input for Month in cell B2 is from a data validation drop-down list of values from 1-12. Merely done for convenience.
Input for Week in cell C2 is from a data validation drop-down list of values from 1-4. These are the only values that work correctly in the formula.
Input for Day in cell D2 is from a data validation drop-down list of values from 1-7 that use the “ddd” custom cell format to convert those numbers into an actual date. (You can’t actually type in a text value because what you’re seeing is a formatted date. Below I’ll show an alternate formula where you can use text values like Sun, Mon, … Sat.)
The Formula
The formula for Date Selected in cell E2 is uses the “ddd, m/d/yyyy” custom date format (US) and the formula is:
This formula has two parts, subtracting the WEEKDAY from the DATE. They both hinge on the fact that WEEKDAY function values are 1-7 for days Sun-Sat, and that an inverse relationship exists for the first week of the month, as shown by the chart. (Eight minus the WEEKDAY value)
Column three is the Day value, or 3rd argument, of the DATE function inside the WEEKDAY function. This relationship works for every day of the week. For a Tuesday, we have WEEKDAY(DATE(Year, Month, 5)). For a Thursday we have WEEKDAY(DATE(Year, Month, 3)).
The DATE Function relies on the Week number 1-4 to generate a date that is one day past that particular week. C2 * 7 +1, is just giving values 8, 15, 22, or 29.
So for a Thursday Thanksgiving in the USA you have
DATE(Year, 11, 29 – WEEKDAY(DATE(Year, 11, 3)))
and for a Tuesday Thanksgiving in Canada you have
DATE(Year, 10, 15 – WEEKDAY(DATE(Year, 10, 6)))
It helps to visualize this formula in the Evaluate Formula dialog box and step through the formula iteration.
The Data Validation
One critical aspect of this formula is to get a numerical date value from cell D2 for the DAY function. I use values 1-7 and convert them to Dates by custom formatting the cells with the “ddd” format.
Of course this resulted in my date range being 1 Jan, 1900 to 7 Jan, 1900 and is okay for the Windows Date System.
Note: Excel for Mac users check to make sure you’re not using the 1904 Date System, in Excel>Preferences>Calculation look under the Workbook options to see if Use the 1904 date system is unchecked.
If you want to make this work with either date system (Windows or Mac) then choose a month where Sunday is the first day, like 1 Aug, 2010. You’ll need seven dates ending with 7 Aug, 2010.
Alternate Formula Without Custom Formatting
You can, of course, skip the custom formatting for the Day in cell D2 by using straight text values in the data validation list.
This requires a different formula and below I’m utilizing the VLOOKUP function.
Excel is a great tool for figuring stuff out, like for conversions that aren’t easy to do in your head. Here I’m converting Pace to MPH and then reversing the process, converting MPH to Pace, to create a conversion chart.
My Conversion Problem for Minutes per Mile to MPH
I track my Average Pace when out walking for exercise by using the iPhone App Walkmeter, then log that information into the Lose It App. The problem I have is converting my Average Pace to Miles per Hour (MPH).
Below you can see my average pace is 13:22 per mile, but Lose It wants me to pick from a list of MPH values.
Simple Conversion Equation
You can use algebra to work out how to convert Pace, in minutes per mile, to MPH.
The problem with this is that Walkmeter shows the Average Pace in Minutes and Seconds per mile, which is not decimal minutes per mile.
Converting Minutes and Seconds
There are a couple of ways to convert minutes:seconds to decimal minutes. The first mimics what I would do by using a calculator and the second is strictly an Excel thing to convert minutes per mile to MPH.
Decimal Minutes
Divide the seconds by 60 then add the result to the number of minutes to get decimal minutes.
B2 =MINUTE(A2)+SECOND(A2)/60
This solution uses the Excel Functions MINUTE and SECOND.
Decimal Hours
Convert 13:22 to a time serial number by using the TIME Function, then multiply by 24 to get decimal hours.
B2 =TIME(,MINUTE(A2),SECOND(A2))*24
The TIME Function above has three arguments:
Hour, which is blank
Minute, which uses the MINUTE Function
Second, which uses the SECOND Function
Convert to MPH
Now that I’ve converted minutes and seconds to either decimal minutes or hours, converting Pace to MPH can be completed in a second step.
Using Decimal Minutes
Simply divide 60 min/hr by the 13.37 pace/mile to get 4.49 MPH.
C2 =60/B2
Combining equations in cells B2 and C2 gets us the conversion in one big equation:
MPH =60/(MINUTE(A2)+SECOND(A2)/60)
Using Decimal Hours
Here we simply invert the decimal hours to get our answer, one (1) divided by 0.0222778 decimal hours, gives us 4.49 MPH.
C2 =1/B2
Again, we can combine equations to get:
MPH =1/(TIME(,MINUTE(A2),SECOND(A2))*24)
MPH to Pace Conversion
For a given set of MPH values I want to convert to Pace per mile and show the result in a minutes:seconds format. Essentially reversing what I just did above.
The simplest way to do this is to realize that the time serial number is based on seconds. We’ll also use the fact that 1 hour = 3600 seconds.
When we divide 3600 by an MPH value, it gives us the number of seconds it takes to go one mile. Plugging these seconds into the TIME Function will give us our answer, but as a time serial number. We can then use a custom format of mm:ss for the Pace range and the conversion is complete. The equation for cell B2 is:
=TIME(,,3600/A2)
This formula works because column B is formatted using the mm:ss custom format.
So now I have a conversion chart for MPH to Pace and can probably remember that a pace of 13:22 is close to 4.5 MPH, which is helpful to me. How about you?
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.
Decimal to Time Conversion
Here are three simple steps to convert decimal to time.
Enter a decimal value into a cell
Divide by 24
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.
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.
Excel 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.
However in Excel 2010, when you enter the WEEKDAY Function into a cell, they are shown as you enter the second argument.
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.
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.
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.
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.
I use the Woopra desktop app to view live visitors to this WordPress blog. Visitors will see a Woopra window lurking about when I’m logged into my (free) Woopra account. One option is to initiate a chat session with me.
I used a column of Start Dates for the past year and happened to notice a pattern with the Days column. Every month in the DATEDIF Function seems to have 31 days.
