Excel functions to C# code

Question

So I'm trying to convert this Excel cell calculation into C#, but I can't really make sense of what it's doing. I read over the EDATE definition and it still doesn't make too much sense.

IF(EDATE(B25,-12)>A25,((EDATE(B25,-12)-A25)/(EDATE(B25,-12)-EDATE(EDATE(B25,-12),-12)))+1,(B25-A25)/(B25-EDATE(B25,-12)))

B25 = End Date
A25 = Start Date

It's essentially trying to calculation a fraction of a year. Should be very easy to do, but I'm not entirely sure what this EDATE thing does.

office.microsoft.com/en-us/excel-help/edate-HP005209073.aspx this link will explain what EDATE is in Excel — MethodMan
– MethodMan, Commented Dec 20, 2011 at 22:03
... from which it's clear EDATE is DateTime.AddMonths(). — GSerg
– GSerg, Commented Dec 20, 2011 at 22:05

Marc · Accepted Answer · 2011-12-20 22:29:56Z

6

According to EDATE, you're mostly dealing with 12 months before the End Date (B25).

Given that, this seems to say:

If the start date is more than 12 months before the end date then:

(The amount of time that the start date is prior to the year before the end date divided by one year) + 1

Else:

The amount of time that the start date is prior to the end date divided by one year.

I really don't know how Excel handles date arithmetic or what the point of this function is, but that's my pseudo at a glance.

Really, it's just checking the if condition I mentioned, then offsetting the entire arithmetic by a year if the condition is true.

Edit

Okay, some quick research shows that Excel does date arithmetic purely as days, so then 12/1/1900 - 1/1/1900 = 335 days. Putting a time on either date makes it a fraction of a day.

Given that, this Excel formula appears to attempt to calculate the fractional year difference between the two dates.

This is a rough piece of code that should provide it:

TimeSpan span = endDate.Subtract(startDate);
double years = span.Days / 365.25;

edited Dec 20, 2011 at 22:29

answered Dec 20, 2011 at 22:05

Marc

9,3742 gold badges32 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

slandau Over a year ago

This is great, thanks so much for your help. But what if some years have 366 days and others have 365. The 365.25 would be trying to even that out, however, what if the difference is say between 5/26/2011 and 6/2/2012?

slandau Over a year ago

Would something like this work? toDate.Subtract(new DateTime(toDate.Year - 1, toDate.Month, toDate.Day)).Days as far as the denominator goes, but use the Subtract to get the numerator.

Marc Over a year ago

@slandau: that is closer to the original and better if your goal is to come up with the same answer as Excel. I'm not sure it's the right answer though. What if the toDate's year doesn't encompass Feb 29, but the difference between startDate and toDate does encompass a leap year? Suddenly, you're off again. So, the answer to your question is, what is the requirement? Hope that helped.

Collectives™ on Stack Overflow

Excel functions to C# code

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related