CODEWITHSUNDEEP
c#.netexcel
slandau
slandau

Reputation: 24102

Excel functions to C# code

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.

Upvotes: 3

Views: 1239

Answers (1)

Marc
Marc

Reputation: 9354

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;

Upvotes: 6

Related Questions