Computing desk
< April 19	<< Mar \| April \| May >>	April 21 >

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.

April 20

Date calculations in Microsoft Excel: how to calculate the difference between two dates

Does Microsoft Excel have any way to perform the following date calculation? I have an "earlier date" (let's just say, June 27, 1907). I have a "later date" (let's just say, February 5, 2011). And I want a calculation that tells me how many years, months, and days have elapsed between the two dates. So, I want my "final answer" to be something like: "103 years; 7 months; and 9 days" (or whatever the actual calculation is). What I normally do is this: I take the earlier date and "minus" it from the later date. In this case, that gives me 37,844 days. I take that value (37,844 days) and divide it by 365.25 days (i.e., one year). And, in this case, that gives me 103.6112 years. So, for various reasons, this method is not "good enough" for my purposes. First, I'd like an "answer" that is reported back as "xx years; xx months; and xx days" format (as opposed to "103.6112 years"). Second, I want the computer to perform an exact calculation, taking into account leap years, etc. (My division by 365.25 is just a simple and generic approximation. The exact results would depend on whether or not the specific dates did -- or did not -- entail leap days.) So, is it possible to get what I want from Excel? Thanks. Joseph A. Spadaro (talk) 03:37, 20 April 2017 (UTC)[reply]

Not to my knowledge. You can format the cell containing the result to show what the date would be if that amount of time had elapsed since the stroke of midnight, December 31st, 1899. Or use something like this to figure it out.

ᛗᛁᛟᛚᚾᛁᚱPants Tell me all about it.

05:02, 20 April 2017 (UTC)[reply]

It looks like you can use the DATEDIF function to do this. It may depend on which version of Excel you are using.--Phil Holmes (talk) 07:59, 20 April 2017 (UTC)[reply]

I am using Microsoft Office 2016. I looked at that "DATEDIF" function. But, that does not do what I am looking for. That simply tells me how many years (or how many months; or how many days) have elapsed between two dates. As far as I can tell, that is. And I can already do that with the simple "minus" calculation that I referred to above. Joseph A. Spadaro (talk) 11:46, 20 April 2017 (UTC)[reply]

Follow-up: I found this page on the internet: How to use dates and times in Excel. A little bit down the page, there is a section entitled "How to Find the Number of Days, Months, and Years Between Two Dates". It has an extremely unwieldy formula. But, it does work. Thanks. Joseph A. Spadaro (talk) 12:01, 20 April 2017 (UTC)[reply]

Yeah, it looks like a hot mess, but it's actually simple. You just work through subtraction, starting at years, then doing months, then days. Honestly, I wouldn't bother. I'd take a guess and punch it into a date calculator, then refine my guess based on that, and have it done in a minute or two. But I'm impatient like that, more interested in results than in reusability.

ᛗᛁᛟᛚᚾᛁᚱPants Tell me all about it.

12:28, 20 April 2017 (UTC)[reply]

Thanks. Yes, I agree. But, I need this calculation for a lot of dates (not just for one or two dates). So, I can't be bothered to go through a "trial and error" for each and every one that I need. I wanted a formula that will do all the calculations for me. I agree. If I only needed this for one or two dates, I would have used your method of "trial and error" and refining the results. Thanks. Joseph A. Spadaro (talk) 13:08, 20 April 2017 (UTC)[reply]

Apparently, you found a formula that works (even though it is ugly), but depending on what you use the results for, you should be wary that "months" and "years" are not fixed-length units of measurements, because of leap years and non-fixed month length. For instance, the formula will say there is "one month and zero days" in the interval February 1, 2017 - March 1 2017, and the same for March 1 2017 - April 1 2017, but the latter is 31 days instead of the former's 28.

I don't think I taught you anything, but if you are looking at the "days" part in "10 years, 8 months and 5 days" you probably want a good precision to compare the dates, which that phenomenon will destroy. Tigraan^{Click here to contact me} 13:58, 20 April 2017 (UTC)[reply]

Thanks. Yes, exactly. And I had assumed (wrongly) that Excel would have some "simple" calculation for all of this. One that would, in fact, take into account leap years, leap days, different month lengths, etc. It seems that it (Excel, that is) does not. If doing one or two calculations, it's probably just easier to do the whole mess manually. Thanks. Joseph A. Spadaro (talk) 14:10, 20 April 2017 (UTC)[reply]

Make sure not to work with dates before March 1, 1900, because the Windows version of Excel falsely believes that February 29, 1900 existed in the Gregorian calendar. Jc3s5h (talk) 14:10, 20 April 2017 (UTC)[reply]

(edit conflict)If you want to store it in a vba module, try this:

'this function performs the calculations
Private Function GetDiffInYYMMDDFormat(earlyDate As Date, laterDate As Date) As String
    Dim years As Integer
    Dim months As Integer
    Dim days As Integer
    Dim container As Date
    
    container = laterDate - earlyDate
    years = Year(container) - 1900
    months = Month(container) - 1
    days = Day(container) + 1
    
    GetDiffInYYMMDDFormat = "" & years & " years, " & months & " months, " & days & " days"
End Function

'call this sub to get the result
Public Sub CalcuateDateDifference()
    'replace "A1" and "A2" with the actual cells that contain the earlier and later dates, respectively
    ActiveCell.Text = GetDiffInYYMMDDFormat(Range("A1").Value, Range("A2").Value) 
End Sub

I'd have offered this last night, except I use Open Office at home, and didn't want to bother proof-reading my code for syntax errors and proper capitalization. This actually obfuscates the leap year, but as the format you said you wanted doesn't account for leap years, this isn't actually a problem. Also, if you're going to abstract this back to a count of days for any reason, you will need to add 1 day if the date range crosses the millennium. (2000 was an the rarest year in the leap year formula. $ᛗᛁᛟᛚᚾᛁᚱPants Tell me all about it.$ 14:49, 20 April 2017 (UTC)[reply]

@MjolnirPants: Thanks. But ... huh? I have no idea what any of that means? I don't even know what "vba module" means. I just know how to use Excel, and just the basics at that! What is this "vba module" stuff you are talking about? And what does that have to do with Excel? Thanks. Joseph A. Spadaro (talk) 23:32, 20 April 2017 (UTC)[reply]

Press Alt+F11 when you have your workbook open to open the VBA IDE, then near the upper left of that window you'll see a list of all the sheets in your workbook. Double click on the sheet you're using, then paste that code in the big writing window. Then, replace the "A1" and "A2" as indicated (be sure not to delete the quote marks!) with the address of the cells holding your dates, select the cell you want to hold the result (you'll have to move the VBA IDE window aside), then go back to the VBA window, make sure your cursor is on the line that starts ActiveCell.Text = and hit the play button near the top of the VBA window.

Or send me an email and I'll email you an empty workbook set up to do the calculations (though be warned: I can't do excel with VBA over the weekends because I don't have excel at home).

ᛗᛁᛟᛚᚾᛁᚱPants Tell me all about it.

23:45, 20 April 2017 (UTC)[reply]

@MjolnirPants: Thanks. I will try that when I have some free time, a bit later. So, you are saying that this "vba module" is essentially a component or a feature within Excel? Is that correct? (I never heard of it until just now.) Thanks. Joseph A. Spadaro (talk) 00:27, 21 April 2017 (UTC)[reply]

Yes, it's how excel does all the 'advanced' stuff it's capable of. Google excel vba for more. The programming language used (VBA stands for Visual Basic for Applications) is actually quite simple, once you get used to it. It's based on a language called BASIC, which was invented for the specific purpose of both teaching programming, and allowing brand new programmers to write code.

ᛗᛁᛟᛚᚾᛁᚱPants Tell me all about it.

02:46, 21 April 2017 (UTC)[reply]

Thanks. I will have to check it out. Like I said, I never heard of this till just now. Thanks! Joseph A. Spadaro (talk) 03:51, 21 April 2017 (UTC)[reply]

Ooh, this one gave me the chance to dust off my Excel hat after far too long:

=DATEDIF(A1,B1,"y")&" years, "& DATEDIF(A1,B1,"ym") &" month(s), "& DATEDIF(A1,B1,"md")&" days"

A1 is the start date, and B1 is the end date. Alter these cell references as necessary, or replace with static dates :) Liquidborn (talk) 15:07, 24 April 2017 (UTC)[reply]

Comparing time series

I have two sets of time-stamped data that I need to compare in Excel. One set ("real" data) was logged at 2 second intervals initially, then switches to logging at 10-minute intervals (and just to be awkward, this is at hh:02, hh:12, hh:22, etc). The other set (generated by computer prediction) has values every 10 whole minutes (hh:00, hh:10, hh:20, etc). Is there an easy way to compare the predicted data against the closest real data?Iapetus (talk) 14:25, 20 April 2017 (UTC)[reply]

I'm not real clear on what you're describing. Could you expound some more? Don't worry if it takes a wall of text, I'll still read it.

ᛗᛁᛟᛚᚾᛁᚱPants Tell me all about it.

14:52, 20 April 2017 (UTC)[reply]

I have two sets of data (tide data, to be precise). One set is actual, measured tide height data. The other is predicted tide heights for the same time and place. I want to compare them (calculate difference, etc) to assess how well the prediction matches reality. The predicted data consists of a series of values with a time stamp (date and time) every 10 minutes, e.g.:

2016/09/01 16:40 0.318
2016/09/01 16:50 0.315
2016/09/01 17:00 0.312

The real data is similar, but the recording interval is different (and changes part way through the data set):

2016/09/01 16:45:30 1.256
2016/09/01 16:45:32 1.255
2016/09/01 16:45:34 1.243
...
2016/09/01 23:02:00 1.439
2016/09/01 23:12:00 1.446
2016/09/01 23:22:00 1.462

For each epoch in the "predicted" file, I need to find the closest in time "real" value, so I can compare the height values. Is there an easy way to do this in Excel?Iapetus (talk) 15:41, 20 April 2017 (UTC)[reply]

You'll probably need VBA for that. I can write the code easily enough if you post a decent sized sample. Let's say one day's worth.

ᛗᛁᛟᛚᚾᛁᚱPants Tell me all about it.

15:55, 20 April 2017 (UTC)[reply]

Tides follow a sinusoidal path (technically the sine wave caused by the Moon plus the sine wave caused by the Sun), so you should fit the predicted values to such a curve, then use that curve to find the heights at the actual times when samples were taken. Then compare those heights to the actual heights observed. (I say to use the predicted values to plot the curve, since they presumably won't be influenced by other factors, like waves, unlike the actual measurements.) As far as analyzing the results, if your actual readings are consistently higher or lower than the predictions, at certain times of the day, month, or year, then there's something wrong with your model. If your predictions are a bit high just as often as they are too low, with no apparent pattern, then it's probably a good model. StuRat (talk) 16:35, 20 April 2017 (UTC)[reply]

For a real roundabout method, you can round the timestamp to the nearest value you want, I found a few formulas you can check out here. You'll have to play around with the exact numbers to get it to what you want. Then use pivot tables to compare the two. uhhlive (talk) 18:28, 20 April 2017 (UTC)[reply]

to be clear, the code I'm offering to write (I want a good size sample just to check it) will plot the existing predictions and use that plot to generate a new prediction for each exact time of measurement. It's won't be perfect without access to the prediction algorithm, but it'll be closer than comparing times that could be off by up to 5 minutes. $ᛗᛁᛟᛚᚾᛁᚱPants Tell me all about it.$ 18:36, 20 April 2017 (UTC)[reply]

I've found a simpler solution: 1) create a new column containing the times rounded to the nearest 10m, using the formula =round(T/X,0)*X (where T is the time and X is 10 minutes in decimal days. 2) Then use Vlookup to find corresponding values. Iapetus (talk) 12:35, 21 April 2017 (UTC)[reply]

That will leave you with the same > 5min disparity I mentioned above, but if that's not important, then there you go.

ᛗᛁᛟᛚᚾᛁᚱPants Tell me all about it.

12:55, 21 April 2017 (UTC)[reply]