Convert Hours, day and week values in minutes

1. Convert Hours, day and week values in minutes

Hi All,

I have to deal with outlook task dumps and get a history of work done over several years.

For one year's worth, I have over 8000 tasks to process, and I was wondering if there was a macro I could use.

Data being handled by the macro would be the Total Work done per task, which can be in the form of:

15 minutes,
1 hour,
3 hours,
1 day,
2 days
1 week
5 weeks

I would like a macro that could process it so that the 3 hours example would be something like this

3 hours
If hours multiply 3 by 60 = 180

And if 15 minutes, remove the minutes to end up with 15

Where can I get what I need/is there existing code/or can one of you excel gods help me?

Thanks!

2. Re: Convert Hours, day and week values in minutes

Hi Jon,

Can you attach a sample file with your list of possible numbers and words that might appear. This looks like a modified VLookup() table answer. They aren't that hard.

To attach a sample file, click on "Go Advanced" and then the Paper Clip Icon above the advanced message box.

3. Re: Convert Hours, day and week values in minutes

Book2.xlsx

Here is the file

4. Re: Convert Hours, day and week values in minutes

OK - see the formula of:

=LEFT(A2,FIND(" ",A2))*VLOOKUP(TRIM(MID(A2,FIND(" ",A2),LEN(A2))),\$D\$1:\$E\$6,2,FALSE)

in the attached. I created a table also to convert those words to numbers..

5. Re: Convert Hours, day and week values in minutes

that's quite nice, a coworker also showed me the text-to-columns function that works quite nice to separate number from text, and I just if functioned my way to victory.

How could I get the text-to-columns and my if statement in a macro?

Here is if statement

=IF(LEFT(C6,3)="wee",B6*5*7.5*60,IF(LEFT(C6,3)="day",B6*7.5*60,IF(LEFT(C6,3)="hou",B6*60,IF(LEFT(C6,3)="min",B6,B6))))

6. Re: Convert Hours, day and week values in minutes

You may also try this to see if this works as per the requirement.

Option Explicit
``Please Login or Register  to view this content.``

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1