how do I convert to hours?

I want to convert the below column value into hours, can you please help?

2 weeks, 1 day, 6 hours, 30minutes

(1 week = 5 days) the above column should give me result in another column in hours. Please help me with formula.
Surendar SAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
So are you saying the cells you want to convert have text in them, and the format of that text is always:

2 weeks, 1 day, 6 hours, 30 minutes

with that spacing, and commas, etc?  And is that always true?  And will all 4 pieces always be present, even if say minutes was 0?


»bp
0
Bill PrewIT / Software Engineering ConsultantCommented:
This would be a lot easier / simpler in a VBA macro function that you could then call from a cell formula, but since you specifically asked for a formula approach, here is a sheet with the formula in it and a couple of test cases.

The text field must be of the following format or an error will be generated:

aaa weeks, bbb days, ccc hours, ddd minutes

EE29066936.xlsx


»bp
0
Surendar SAuthor Commented:
Hi Bill,

First of all thank you for your help.
These are the some possibilities for the column.

Column A
30 minutes
1 day, 1 hour
2 days, 30 minutes
1 week, 2 days, 30 minutes
3 weeks, 1 day, 1 minute
4 weeks, 2 days
1 week

Need to show the hour in Column B as you know.
And 1 day considered as 8 hours, did you already applied the logic in the formula?
can you give me the formula for the attached file? Thanks in advance.
For the empty cells, we need to show as 0 minutes/leave them as empty.
Hours-Calculation.xlsx
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Bill PrewIT / Software Engineering ConsultantCommented:
Okay, here is an update that should handle all of those, make sure the numbers are what you wanted.  The formula is in the attached spreadsheet, and looks like this:

=IFERROR(LEFT(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))), (0)*LEN(A2)+1, LEN(A2))), FIND(" ", TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))), (0)*LEN(A2)+1, LEN(A2)))))*INDEX({2400,2400,480,480,60,60,1,1},MATCH(TRIM(MID(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))), (0)*LEN(A2)+1, LEN(A2))), FIND(" ", TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))), (0)*LEN(A2)+1, LEN(A2)))),999)),{"week","weeks","day","days","hour","hours","minute","minutes"},0)),0)+IFERROR(LEFT(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))), (1)*LEN(A2)+1, LEN(A2))), FIND(" ", TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))), (1)*LEN(A2)+1, LEN(A2)))))*INDEX({2400,2400,480,480,60,60,1,1},MATCH(TRIM(MID(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))), (1)*LEN(A2)+1, LEN(A2))), FIND(" ", TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))), (1)*LEN(A2)+1, LEN(A2)))),999)),{"week","weeks","day","days","hour","hours","minute","minutes"},0)),0)+IFERROR(LEFT(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))), (2)*LEN(A2)+1, LEN(A2))), FIND(" ", TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))), (2)*LEN(A2)+1, LEN(A2)))))*INDEX({2400,2400,480,480,60,60,1,1},MATCH(TRIM(MID(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))), (2)*LEN(A2)+1, LEN(A2))), FIND(" ", TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))), (2)*LEN(A2)+1, LEN(A2)))),999)),{"week","weeks","day","days","hour","hours","minute","minutes"},0)),0)+IFERROR(LEFT(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))), (3)*LEN(A2)+1, LEN(A2))), FIND(" ", TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))), (3)*LEN(A2)+1, LEN(A2)))))*INDEX({2400,2400,480,480,60,60,1,1},MATCH(TRIM(MID(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))), (3)*LEN(A2)+1, LEN(A2))), FIND(" ", TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))), (3)*LEN(A2)+1, LEN(A2)))),999)),{"week","weeks","day","days","hour","hours","minute","minutes"},0)),0)

Open in new window


Hours-Calculation.xlsx


»bp
1
Bill PrewIT / Software Engineering ConsultantCommented:
And just comparison here is an approach using a User Defined Function in VBA macro to do the calculation.  Spreadsheet attached with a new column calculated using this approach, and containing the macro shown below.

Public Function CalcMinutes(strText As String) As Variant
    Dim arrChunk() As String
    Dim strChunk As Variant
    Dim arrWord() As String
    
    CalcMinutes = 0
    If strText = "" Then Exit Function
    
    arrChunk = Split(strText, ",")
    For Each strChunk In arrChunk
        arrWord = Split(Trim(strChunk), " ")
        If UBound(arrWord) <> 1 Then
            CalcMinutes = CVErr(xlErrNA)
            Exit Function
        End If
        Select Case LCase(arrWord(1))
            Case "minute", "minutes"
                If IsNumeric(arrWord(0)) Then
                    CalcMinutes = CalcMinutes + CInt(arrWord(0))
                Else
                    CalcMinutes = CVErr(xlErrNA)
                    Exit Function
                End If
            Case "hour", "hours"
                If IsNumeric(arrWord(0)) Then
                    CalcMinutes = CalcMinutes + (CInt(arrWord(0)) * 60)
                Else
                    CalcMinutes = CVErr(xlErrNA)
                    Exit Function
                End If
            Case "day", "days"
                If IsNumeric(arrWord(0)) Then
                    CalcMinutes = CalcMinutes + (CInt(arrWord(0)) * 480)
                Else
                    CalcMinutes = CVErr(xlErrNA)
                    Exit Function
                End If
            Case "week", "weeks"
                If IsNumeric(arrWord(0)) Then
                    CalcMinutes = CalcMinutes + (CInt(arrWord(0)) * 2400)
                Else
                    CalcMinutes = CVErr(xlErrNA)
                    Exit Function
                End If
            Case Else
                CalcMinutes = CVErr(xlErrNA)
                Exit Function
        End Select
    Next
End Function

Open in new window

Hours-Calculation.xlsm


»bp
1
Surendar SAuthor Commented:
Hi Bill,

Thank you very much!! I am wondering by seeing that formula, It is working as expected :)
Can I ask you one more help?
Please find the attached file, we need to calculate the total time spent based on Main ticket# in the sheet of Main by comparing the same to the next sheet of Sub.

1 week = 5 days
1 day = 8 hours as you know.


Main sheet > C2 = addition of Sub sheet > C column based on Main sheet > Main ticket column.

Please give me the result C2 same like B2 by calculating all the sub tickets in the next sheet based on Main Ticket.
Remaining time we need calculate B2-C2 = D2

Thanks,
Surendar
Hours-Calculation.xlsx
0
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, added that in, see how this looks.

Hours-Calculation.xlsx


»bp
1
Surendar SAuthor Commented:
Hi Bill,

Thank you, sorry I should have informed you earlier.
I need C,D and E column in the format of B column > weeks,days,hours,minutes

1 week - 5 days
1 day - 8 hours

Can you please help.

Thanks,
Surendar
0
Surendar SAuthor Commented:
Sorry for the re-work Bill, the last request should be my final one.
I am very thankful for you.

Thanks,
Surendar
0
Bill PrewIT / Software Engineering ConsultantCommented:
Working it...


»bp
1
Bill PrewIT / Software Engineering ConsultantCommented:
These formulas are getting very large and complicated, are you sure you want to solve this with formulas rather than VBA user defined functions?


»bp
0
Bill PrewIT / Software Engineering ConsultantCommented:
Here is what I have so far.  The calculated columns in hours (highlighted in yellow) are needed to drive the "text" versions of those, but you can of course hide those columns so they aren't visible.

(I did adjust some of the data on the second sheet just to get some more interesting values on the first sheet)

Hours-Calculation.xlsx


»bp
1
Surendar SAuthor Commented:
Hi Bill,

Thank you :)

Seems we need to correct minus values, when it crosses the original estimation.
Here is the output I have, please find the attached file.
I have added some comments for you, if possible can you please help on that.

Thanks,
Surendar
Hours-Calculation.xlsx
0
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, see how this looks.

Hours-Calculation2.xlsx


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Surendar SAuthor Commented:
Hi Bill,

I don't see formula in the excel, can you please check.
The result in the excel looks fine.

Thanks,
Surendar
0
Bill PrewIT / Software Engineering ConsultantCommented:
Formula is there, but I left it as multi-line format since that makes it much easier to follow.  I typically work with large formulas like this in a text editor and then paste in to Excel.

To see the full formula you can expand the formula bar by using the small v icon (see picture below).

Or, when on the cell you can press F2 and see the full formula on top of the spreadsheet.

sshot-351.png

»bp
1
Surendar SAuthor Commented:
Thanks a lot :)
You are great!!!! I don't know what to say. I will never forget your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.