Unable to select the correct formula (IF or VLOOKUP) to get results in column B

I think you would probably need to use another column, in which you could extract the values for days, hours, minutes etc. and convert them to some numeric value (this column could be hidden), and then use this number to search a lookup table to return the desired result.

Hope this helps.

Pete

Hello AliGW,

Thanks for the tip!
I've added the excel file as well.
Basically there is no formula behind those columns.

Originally Posted by Pete_UK

I think you would probably need to use another column, in which you could extract the values for days, hours, minutes etc. and convert them to some numeric value (this column could be hidden), and then use this number to search a lookup table to return the desired result.

Hope this helps.

Pete

Hello Pete,

Thanks for the quick reply!
I was also thinking that I need to convert the column A into numeric time, but I'm not very sure how that would look like with the actual data I have inserted in column A.
For example, I don't know how to transform into time the information "2 days 17 Hours 43 Minutes", etc. Especially since the data might differ each day. :-?

Insert a new column B in the file you attached, and put this formula in B2:

=IFERROR(LEFT(A2,FIND("Day",A2)-1)*24*60*60,0) + IFERROR(MID(0&A2,FIND("Hour",0&A2)-3,3)*60*60,0) + IFERROR(MID(0&A2,FIND("Min",0&A2)-3,3)*60,0) + IFERROR(MID(0&A2,FIND("Sec",0&A2)-3,3)*1,0)

and copy this down. It will convert the entries in column A into seconds, and then you can use this to determine the results that you want in column C.

Hope this helps.

Pete

Sorry, I should have given you the rest of it. Put these values in cells G2 to G7 (after inserting that new column B):

0
1
18000
86400
432000
432001

Then you can use this formula in C2:

=VLOOKUP(B2,$G$2:$H$7,2)

Copy this down as required.

Hope this helps.

Pete

Hello Pete,

Thanks for the tip!
I'll try what you've suggested.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete