Covert Years-Months ago date format to no of days/minutes

[webcetdotin]

Hi, Can anybody help me in converting this excel logic to macro or formula:

Requirement- Convert Months/Years/Days/Hours ago time to Minutes., and give ranking to it.

I Have excel file with colmn Time and Download count in column D and E, i want to find out average download time per minute with a macro or formula and give Top rank based on the most downloaded file in less time. I have excel file data as follows:

**Input Data**

Filename;TIME;DOWNLOADS
- A;3 months ago;917,599 Downloads
- B;2 years ago;296,520 Downloads
- X;5 months ago;39,695 Downloads
- Z;9 months ago;15,851 Downloads

**EXPECTING RESULTS OUTPUT:**

- SNO;Filename;TIME;DOWNLOADS;TIMEinMins;Downloads;AVGDownloads;DRank
- 1;A;3 months ago;917,599 Downloads;131400;9759;13.46449431;4
- 2;B;2 years ago;296,520 Downloads;1051200;29652;35.45123432;3
- 3;X;5 months ago;39,695 Downloads;219000;3969;55.17762661;2
- 4;Z;9 months ago;15,851 Downloads;394200;1585;248.7066246;1


**Logic:**

3 months ago uploaded File A has 917,599 Downloads. 2 years ago uploaded File B has 296,520 Downloads. I want to give the best ranking to files based on AVG DOWNLOADS/UPLOADED TIME

so i converted uploaded time to minutes with current time , avg downloads per minutes = Total no of Downloads/Total number of Minutes till now

I gave the results avg downloads to best ranking in lesser time.

**Notes:**

* Some values in Download data have comma(,)Ex;- 296,520
* Time column shows months ago or years ago or hours ago or minutes ago or seconds ago, which should be converted to minutes.

 

[Combat Womble]

Hi
Recap: you have time in column D and number of downloads in column E. you want time into minutes and ave downloads per minutes.
In column H1 put = NOW()
Select column G, format cells and select number.
In column G1 put =(H1-D1)*24*60 [this will give you minutes]
In column F1 put =E1/G1 [this will give you the ave downloads per minute]
In column I1 put =RANK(F1,$F$1:$F$10000)
Highlight F1 to I1 and drag it down to the last row of your data.

If you have headers you'll need to change the row numbers.

Hope this helps