amount divided by years and get per month valve.

[noubaba]

I am looking for a excel formula:

Lets suppose I have some earnings (amount) in one cell and 1 year, 8month and 10 days is mentioned in other cell. I want to divide available earnings (amount) by 1 year, 8month and 10 days and want to get earnings per month, please help me to fabricate excel formula for that. Thanks in advance.

 

[frank_AL]

noubaba, welcome to the Forum!

You say in your post "1 year, 8month and 10 days is mentioned in other cell". Is there any specific structure in how these data points for year, month and days is listed in this cell?
It is easy to create a formula if those values are listed in separate cells and it is maybe possible if they are in a single cell but there would need to be consistency in how they are listed/mentioned.

Let me know and I will try to help you.

Just for reference, if the value is in cell A2, the year is in B2, the month in C2 and days in D2 this formula in E2 would give you the average monthly value: =A2/((B2*12)+C2+(D2/30.41667))

 

[noubaba]

Thank you Frank to help me on this forum, actually "1 year, 8month and 10 days" is appears in once cells derived from different cell by this formula (=DATEDIF(B49,TODAY(),"y") & " Years, " & DATEDIF(B49,TODAY(),"ym") & " Months, " & DATEDIF(B49,TODAY(),"md") & " Days"),
Further more
purchased date in cell B49 is "Aug/1/2016"
time passed since date of purchase till today is "1 year, 8month and 10 days" appearing in cell B50.
Total earning during this period is 993000 rupees appearing in cell B48.
Now based on above data want to calculate what would be the earning of per month.

 

[frank_AL]

try this formula:
=B48/((TODAY()-B49)/30.41667)
30.41667 is the result of 365/12 which is the average days in a month.
The result of this formula is 48,873.39

If you need a more exact result we would have to determine which month the 10 days (from your formula) falls to to create a more exact fraction of a month.

 

[noubaba]

Thank you Frank, this formula is working fine. Really appreciate your support. Next time would prefer to use this forum rather than to search formulas on Google.