Convert Original Estimate to Days via Excel formula

Question

I am using Confluence JIRA macro with pivoting to show a table of epics and summed  Original Estimates in  1y 2w 3d 4h 5m format.   General readers like this format.  I do a simple copy and paste of this table to excel to help generate a orig Est burnup.   (faster than Jira search/export) I could not find parsing formula to convert Org Est from this format to workdays.   So... I came up with one.  It works perfectly on valid JIRA output but trips on odd cases like "-1y" and a few others (I can send) Would be interested if someone has even a better formula.

Here it is below.  To use: paste formula  into cell then do a find/replace (only in that cell) to replace $E96 with proper Orig Est cell reference.    It looks hairy but it is simply looking for a letter and reading a 1 or 2 digit # in front of that letter, converting all to days and addin' up

=ROUND(IFERROR(MID((" "&$E96),FIND("y",(" "&$E96))-2,2)*260,0)+IFERROR(MID((" "&$E96),FIND("w",(" "&$E96))-2,2)*5,0)+IFERROR(MID((" "&$E96),FIND("d",(" "&$E96))-2,2),0)+IFERROR(MID((" "&$E96),FIND("h",(" "&$E96))-2,2)/8,0)+IFERROR(MID((" "&$E96),FIND("m",(" "&$E96))-2,2)/480,0),3)

Don Hamp_owibjg · Answer

Alexander   Elegant.   I am researching MS Power BI/Power query.   I have never used.       If  you have ink for any step by step for configuring Excel this way for a specific JIRA search I would be eternally grateful.

Alexander Pappert · Answer

if you export the issue search in excel, you will get the value like 25631 seconds. then you only need to convert 1 value.

also, you can save an issue search and import it in excel via import data -> from web and use the power query editor. you can build 1 excel file and with the refresh button you can always load the jira issue search and power query converts the estimate for you.