The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

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.

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.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

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.

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

EE29066936.xlsx

»

First of all thank you for your help.

These are the some possibilities for the column.

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

```
=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)
```

Hours-Calculation.xlsx

»

```
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
```

Hours-Calculation.xlsm»

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

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

I am very thankful for you.

Thanks,

Surendar

»

(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

»

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

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 trialI don't see formula in the excel, can you please check.

The result in the excel looks fine.

Thanks,

Surendar

To see the full formula you can expand the formula bar by using the small

Or, when on the cell you can press

»

Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

2 weeks, 1 day, 6 hours, 30 minuteswith 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