I need a Logic Statement GURU

[NOVUS2017]

I am exporting a report that is giving me time in a sales funnel for each lead. Unfortunately, it spits out this data in one of three forms: X Minutes, X Hours, or X Days.

I want to write a logic statement that says, If the cell end in "Days", multiply the number the cell starts with by 24. If the cell ends in "Hours" multiply the number the cell starts with by 1. If the cell ends in "Minutes", divide the number the cell starts with by 60.

Anyone have any thoughts?

 

[Joe4]

Welcome to the Board!

For a value in cell A1, try this:

=LEFT(A1,FIND(" ",A1)-1) * IF(RIGHT(A1,3)="ays",24,IF(RIGHT(A1,3)="tes",1/60,1))

 

[NOVUS2017]

My mind is blown. I am in awe. Insert gif of crowds bowing before the king. You are impressive, sir.

Welcome to the Board!

For a value in cell A1, try this:

=LEFT(A1,FIND(" ",A1)-1) * IF(RIGHT(A1,3)="ays",24,IF(RIGHT(A1,3)="tes",1/60,1))

 

[Joe4]

Glad I could help!

If you have any questions about any part of it, please let me know.

 

[NOVUS2017]

Formula worked great, but now I'm having a secondary issue. I copied and pasted the formula down the columns for the 1800 rows I have, with my goal being to average them at the bottom. However, many of the cells are blank (being they've spent no time in that category of the sales funnel) so when I pasted in the formula it obviously resulted in the #VALUE! error. Which means I can no longer average the column. I tried just finding and deleting all cells with #VALUE!, the the find function only searches for actual cell contents.

Any solutions for this?

 

[Joe4]

This version should ignore blank cells:

=IF(A1<>"",LEFT(A1,FIND(" ",A1)-1) * IF(RIGHT(A1,3)="ays",24,IF(RIGHT(A1,3)="tes",1/60,1)),"")

 

[NOVUS2017]

You win the internet again. Bonus points for the Calvin and Hobbes.