yearfrac | Difference between dates as a fraction of 1 year | Date Time Utils library

I've got a sheet with Data. I want to calculate the difference between date now and the date which are in cells C3:C10. And the results are stored in cells D3:D10.

That part I got it so far.

But if someone manipulates the values in the result cells then the VBA should recalculate those cells and correct the results.

I want to implement one logic of Excel sheet in oracle database.

I m using YEARFRAC() in Oracle in a stored procedure but I am getting error as

Error(166,22): PLS-00201: identifier 'YEARFRAC' must be declared

Here is my code

I'm relatively new to Python but I have a complicated issue which I think requires a few different functions, but I don't know where to start.

It's kind of straightforward to do in Excel, by using the PRODUCT function, based on a starting point up to the most recent renewal date (in this example it would be 01/10/2020) and simply multiplying this Product function totals of each of the starting point elements, to give the current renewal date total, broken down by element values.

I want to write this in Python but besides the very basic info on Python 3.8 math.prod(), there doesn't seem to be anything more that I can find online to resolve/assist me with this.

I have the following 3 Pandas dataframes (I copied the data from Excel and pasted in the links below for ease of the explanation, but I've imported these as dataframes in my test code).

The data comes in this form which need to be collated together and a revaluation, broken down by the different elements of a member's entitlement, needs to go from the starting date ("StartDate") to the current “renewal” date (01/10/2020) - if possible, there also needs to be a pro-rata done in complete months between the StartDate to the 1st renewal date (equivalent to the Excel YEARFRAC function).

The 3 dataframes are:

1. Identifiers of members with certain basic information. The most important piece of data is the ‘StartDate’ column, as it's the point to revalue member benefits from. It needs to have a function that calculates, I'm guessing here, the TimeDelta (in months) from the StartDate to the member's 1st renewal date, which needs to be a pro-rata of the difference e.g. 1st member started on 01/02/2016 so the 1st renewal needs to have a pro-rata of 8 months. I'd also like to set a boolean where if True, the pro-rata occurs otherwise it doesn't

List of MemberIDs and StartDates

1. Increase factors. The elements will either increase or stay level (never decrease in value)

Increase rates by year (index used is the Renewal Date)

1. Member assets - linked to (1) by the member identifier (MemberID). These different elements should escalate by the increase factors in (2) and these figures are the elements as at the StartDate.

StartDate elements

Essentially, the function(s) need to calculate when the first renewal date is after the StartDate, apply a pro-rata increase if the boolean is True, then work out the range of renewal dates and increase factors between the StartDate and the latest renewal date and finally apply the Product for this date range on the StartDate elements.

With this being my first ever post, my apologies if it's not quite in the format you might expect or with any Python code as I'm very new to Python and to StackOverflow (they wouldn't even allow me to post the tables directly, only as links to images). The data I've provided, due to data protection, are dummy dates but relatively based on the actual data.

Any questions you have, I'm happy to provide more information. Thanks for any help in advance!

EDIT: Please see this temporary solution that I'm using, but it's without the pro-rata of the 1st increase between StartDate to the 1st increase date: Solution (without pro-rata)

• The MemberID in the example, the Python function should find the 1st increase after StartDate as 01/10/2016 (DD/MM/YYYY)
• The function should then check the Renewal Date increases by each element between 01/10/2016 and the latest Renewal Date at 01/10/2020. The Excel PRODUCT formula gives the element increases as: 1.38823, 1.20396 and 1.20462 for elements 1 to 3 respectively
• Finally, the elements from the StartDate are multiplied by the PRODUCT totals of each tranche, to give the current value

If one uses Excel's YEARFRAC(date1,date2,1) function then Excel will use the basis as per the second parameter. This does not matter if date1 and date2 are in the same year, but it does matter if date1 is a leap year (e.g. 2020) and date2 is a non-leap year (e.g. 2021)

I think that a more accurate calculation will take into account that the basis of date1 can be different to the basis of date2.

For instance YEARFRAC(15/12/2020,15/01/2021,1) returns 0.08493151 But the real calculation is (31/12/2020-15/12/2020)/366 + (15/01/2020-31/12/2020)/365 = 0.084811737

I implemented the following in VBA. Does anyone have any fundamentally better way of doing it (either in Excel or in VBA)? (I am not looking for minor improvements to my rushed VBA code)

Call it using yearFracVBA("AA1","AA2")

I have a input box (using C# razor) that user can input a string. I have use ToString("#,#0.00") to format as a numerical value since user will input a numerical value in the input box. When the user press enter it will calculates automatically for example 'average' and generate a percentage which it works. And it will updates the cell. The problem is that I want the input box to accept comma separated (e.g 2,000 or 30,000)in the input box. However, when I did some testing by trying to input numerical value with comma separator, 2,000 will become 2.00. Im trying to figuring out but I couldn't and i am stuck. Can you please help me. Thanks.

This is my html razor code ( I have post the partial code of it since too long):