Wikipedia:Reference desk/Archives/Computing/2014 December 22

Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is an archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.

December 22

Calculating elapsed time between two dates in Microsoft Excel 2013

I often find that I need to do a quick calculation of the time elapsed between two given dates (say, for example, a person's birth date and death date). When I enter the dates into Excel 2013, and I do a "subtraction" function (i.e., I subtract the more recent date from the older date), I always get a result like, for example, 11,287. That figure of 11,287 refers to the number of days between the two given dates. Then I have to take that figure and divide it by 365 (actually, I use 365.25) to convert the number of days to years. My question is: is there an easier (one-step) calculation whereby I can subtract the two dates and get a resulting value that is already given in terms of years? I would think that this would be somehow incorporated into Excel, as I assume that most people typically want to know the number of years (not the number of days) between two given dates. A resulting value of, say, 11,287 days typically has little meaning to most people. Whereas, its equivalent value of 30.9 years is more intuitive and makes more sense to most users. Thanks. Joseph A. Spadaro (talk) 00:22, 22 December 2014 (UTC)

Follow up. Also, is there some function that will give a resulting value such as this (30 years, 7 months, and 12 days elapsed), when I subtract the two dates? Thanks. Joseph A. Spadaro (talk) 00:26, 22 December 2014 (UTC)

Select Cell > Format > Number > Date in the cell that contains your results, and choose one of the predefined date formats, or create your own. This is the relevant page from Microsoft. Tevildo (talk) 01:28, 22 December 2014 (UTC)

Thanks. But, I don't understand what you are saying. From what I understand, your suggestion would offer various formats for a date (for example, 12-25-2104; or December 25, 2014; or Wednesday, Dec. 25, and so forth). How would that be used in the context of what I am asking? Is it possible to do what I have requested with these format options? Thanks. Joseph A. Spadaro (talk) 01:50, 22 December 2014 (UTC)

Note that dividing by 365.25 to convert to years is fine if you only want an approximate value, but if the exact number of years matters, it may not be correct. From January 1 to the following January 1 may be either 365 or 366 days, but it's always exactly 1 year. If you divide by 365.25 you get a number greater than 1 in one case and less than 1 in the other. No doubt this reason is why the value is computed in days in the first place. If the exact number of years is what you want, as in computing someone's age, then you have to work with the years, months, and days as separate components. (I presume Excel gives you ways to do this, but I don't know it myself.) --65.94.50.4 (talk) 01:55, 22 December 2014 (UTC)

Yes, I am aware of the limitations of the division by 365.25 and, yes, I use it only to get an approximate result. Which is why I am asking my above question. I want to avoid that step (of dividing by 365.25). I assume that there must be some way to do what I want in Excel. Joseph A. Spadaro (talk) 05:00, 22 December 2014 (UTC)

One way round the problem would be to convert the date into separate columns of year, month and date but this is a bit messy (use the year, month and day functions. I've achieved a similar result by adding 1 to the cells containing the subtraction then using a custom format of yy mm dd but this doesn't quite display as I expected it to do. I'm sure that someone skilled in VB programming could sort this out, but I'm still on the old version (and not sufficiently skilled to do it easily). Dbfirs 09:26, 22 December 2014 (UTC)

I've just realised that there's a datedif function that takes three arguments (two dates and a required format). This should serve your purpose. It's documented here and here Dbfirs 14:34, 22 December 2014 (UTC)

Thanks. Let me explore that datedif function. I had never heard of that before. It seems to be directly on point. Joseph A. Spadaro (talk) 15:15, 22 December 2014 (UTC)

Thanks, all. Joseph A. Spadaro (talk) 20:00, 26 December 2014 (UTC)

Put me out of my Excel depreciation misery

I am an Excel amateur but have been unfortunately conscripted into helping tidy up a spreadsheet. What I want to do seems to be both too simple and too complicated to fit in with any of the standard Excel options. I know how to write this function; I just don't know how to write it in a form that Excel will actually accept; none of the standard depreciation formulae seem to fit. My situation is as follows...

I have one column giving the quantity of a certain item, another giving the unit cost of that item, and a third column giving a percentage of depreciation for that item—in whole-number form, "20%" expressed as the whole number "20". That was unfortunately done to it by someone else who handled the file; it should have been formatted as a percentage, but I can't convert it without automatically multiplying it by 100. I have thousands of line items and don't have the time to correct this manually, but I need a formula that will salvage the situation as cleanly as possible. The best solution I can think of is to have some term in the function that whittles this number down to a decimal by multiplying it by .01.

I want to create a fourth column which will do the following:

• Multiply the unit cost by the quantity, giving me the total pre-depreciation value of the items (so algebraically something as simple as xy)

• Take the above product and subtract the percentage of depreciation from the final number

The number displayed should be the value of all five Matchbox cars (for example) after depreciation. If I were writing this function on a chalkboard it would look something like

f(x)=x-.01xz

where x is the product of the quantity and the unit cost, and z is the number I have in the depreciation column.

Does that make any sense? I am very sleep deprived at the moment. Any help would be appreciated.71.29.47.112 (talk) 05:53, 22 December 2014 (UTC)

You've already solved the difficult part of the problem. You just need to turn it into an Excel formula. Assuming that the quantity is in column A; the unit cost is in column B; the integer percentage number is in column C; and you want the result in column D; and the first row of values is in row 2; then place in cell D2 the formula: =A2*B2-A2*B2*C2*.01, press enter, then replicate this formula down all your rows by clicking on this cell, moving the cursor over the little square in the bottom right corner until you see a +, then dragging down your thousands of rows (the sheet will scroll down at high speed and it doesn't matter if you go too far provided that you have nothing else in column D). Just click when you reach your n-thousandth line and your formula will be copied to all lines. Dbfirs 08:38, 22 December 2014 (UTC)

You can also use Ctrl+C (or edit/copy) to copy the formula to the clipboard, then select all the cells you want it to be pasted into, and press ctrl+v (or edit/paste). The cells will be filled with formula, with the row/column automatically updated. CS Miller (talk) 11:24, 22 December 2014 (UTC)

Yes, that's probably easier than my method. Dbfirs 14:46, 22 December 2014 (UTC)

Thanks for the replies! I think I've just about got this one figured out. A night of sleep helped.71.29.47.112 (talk) 15:51, 22 December 2014 (UTC)

Since you seem to be in business, it might help to know what you are working on is discount. (There is a different meaning of discount that has to do with bonds that is mentioned in the help for the Excel functions; that's not what you want. I couldn't find anything in Excel help about the kind of discount you're working on.) Depreciation is the amount subtracted from the value of a business asset, like a bulldozer, to keep track of its lower value as it gets older. Jc3s5h (talk) 14:43, 22 December 2014 (UTC)

Nope, depreciation is exactly what I'm dealing with. This is for an insurance claim, so I'm also taking into account depreciation on... well, Matchbox cars were actually a real example. Don't ask me; I just work here. :) 71.29.47.112 (talk) 15:51, 22 December 2014 (UTC)

Opening up new web pages in the same browser.

During a normal day I may work on four different computers, sometimes 5. On most of them when I open a new webpage it is opened in the same browser with a new tab. On one of them the new page is also opened in a new version of the browser, a new window as they say. Also on one of the computers, at least, there was a moment of evolution. I replaced the monitor and the web browser changed the way it operated, instead of opening new pages in separate windows, it began the multiple tab business. What did the monitor have to do with it I still do not know. I hate multiple tabs in the same window. I know I can un-shackle (for the lack of a better word) the new window from the browser and make it free floating but it is an additional hassle. Sometimes I keep adding the tabs with the first one very important, with the others incidental and in the end I forget the value of the first one and I close the whole bunch and this is an inconvenience.

Is there a switch somewhere I can flip to make the browsers open new pages as free floating always?

Thanks, ---AboutFace 22 (talk) 17:05, 22 December 2014 (UTC)

Shift-click should work in most browsers. As for a switch to make this the default, it would depend on what browser you use. CS Miller (talk) 17:34, 22 December 2014 (UTC)

Don't know if this will suit you but before Microsoft was forced to offer other browsers in Europe, I used to carry around Mozilla Firefox, Portable Edition on a stick. It had all my bookmarks and what-have-you, all at the ready. Now they have a new version out, I might start using it again as it works on 8 now.--Aspro (talk) 21:05, 22 December 2014 (UTC)