Wikipedia:Reference desk/Archives/Computing/2018 August 7

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.

August 7

A question about a formula in Microsoft Excel 2016

I have a question about a formula in Microsoft Excel 2016. Cell A-1 has "Tuesday, August 7, 2018" as a person's birthday (today). Cell B-1 has "Saturday, August 7, 1948" as the person's actual date of birth. These cells are both formatted in "Date" format. I want Cell C-1 to calculate the person's age. The age is 70 years old. I want the cell to say "70" (formatted as a number with no decimal points). The formula that I use is Cell A-1 minus Cell B-1. This gives me the value of 25,567. This represents how many days old the person is. I take that value and divide it by 365.25, to (approximately) change the units from "days old" to "years old". And my result is 69.9986311 years old. How can I get the program to determine that the person is exactly 70 years old? As I said, I want to produce an exact value of 70 (formatted as a number, with no decimal points). I do not want to take the "odd value" of 69.9986311 and perform any type of rounding or truncating or such. Thanks. Joseph A. Spadaro (talk) 17:17, 7 August 2018 (UTC)

In my older version of Excel, I just format the result as yy, though this doesn't work for people over 100 years old, and you might like to add a day because of our convention of giving people an integer age even if they don't really reach that age until later on their birthday. The other method that I've used is a lookup table taking account of leap years. Dbfirs 20:36, 7 August 2018 (UTC)

Thanks. So, doesn't Excel have an "easy" and "straightforward" method for determining such an example? Namely, that the span of time between August 7, 1948, and August 7, 2018, is precisely 70 years? Joseph A. Spadaro (talk) 21:05, 7 August 2018 (UTC)

Well, saying that that interval is "precisely 70 years" depends on a definition of a "span of time" that is not at all straightforward or easy to define. For example, the span of time between August 7, 1948 and August 7, 2018 is different than the span of time between August 7, 1946 and August 7, 2016, but I suppose in your application you would want to say both of these are precisely 70 years, even though they differ by 1 day. CodeTalker (talk) 22:24, 7 August 2018 (UTC)

@CodeTalker: Well, yeah, of course. I don't think there would be any controversy or disagreement to state that the time span is "precisely 70 years" (in my August 7, 1948, to August 7, 2018, example). Who would (or could) argue with that fact? The other time-span that you indicate is also precisely 70 years, even if it has an extra day in there, due to a leap year. There is a difference between the definition for "number of days" versus the definition for "number of years". A "day" is just that – a day. A period of 24 hours. A "year" is well-known to sometimes contain 365 days and to sometimes contain 366 days. I don't think it's a controversy or a disagreement to emphatically state that August 7, 2018, to August 7, 2019, is precisely one "year" (whether or not it overlaps with a leap year, and whether or not that "year" contains 365 or 366 days). Joseph A. Spadaro (talk) 23:53, 7 August 2018 (UTC)

You can use the YEARFRAC() function.[1][2] It suggests =INT( YEARFRAC( B1, TODAY() ) ) for keeping it up-to-date. I'm guessing it would still have an issue if the birth day or current day was in a leap year. If the birth day wasn't in a leap year, but the current year was, and it is before leap day. It would divide by 366 for leap years so the percentage would be smaller before leap day. People born on February 29 would have to wait until March 1 on non-leap years. Another issue for born in a leap year and current (non leap year) year's day is after February 28. An alternative would be look at the month and day-of-the-month separately (much longer formula). StrayBolt (talk) 23:43, 7 August 2018 (UTC)

When you say cells A1 and B1 are in Date format, what do you see in the formula bar when you click on these cells? It should show in MM/DD/YYYY format. If that's not the case, then you may need to re-enter the data such that the actual date as seen in formula bar is in MM-DD-YY format (7/7/2018) and it's display format is whatever you need (like 'Tuesday, August 7, 2018). Then formula YEARFRAC(B1, A1) returns 70 as you expect. manya (talk) 04:43, 8 August 2018 (UTC)

@Ranemanoj: Hi. Thanks. The "output" (what I see in my spreadsheet) is "Tuesday, August 7, 2018" for Cell A-1. In the formula bar up above, it says: "08/07/2018". So, I typed in "08/07/2018" into Cell A-1. I then wanted to format the output the way I like. So, then, I clicked "Number". Then, I clicked "Date". Then, I selected the "sample" that says "*Wednesday, March 14, 2012". As a result, my output (what I see on my screen in my spreadsheet) is: "Tuesday, August 7, 2018" for Cell A-1. Similar for Cell B-1. Does this post make sense? Thanks. Joseph A. Spadaro (talk) 05:35, 8 August 2018 (UTC)

@Joseph A. Spadaro Have you tried DATEDIF? In cell C1 enter =DATEDIF(B1,A1,"Y") & " Years, " & DATEDIF(B1,A1,"YM") & " Months, " & DATEDIF(B1,A1,"MD") & " Days" and see if that's what you're after. Matt Deres (talk) 22:12, 10 August 2018 (UTC)

Thanks, all! Very helpful! Joseph A. Spadaro (talk) 16:21, 16 August 2018 (UTC)