Solved

Calculate no of hrs

Posted on 2013-01-15
281 Views
Hi Experts,

In my excel file, I have days in format "5d 5h 30m"

where d = day (8.4 hrs a day)

I need to calculate no of hrs in numeric

the above example should calculate

5d 5h 30m = 5*8.4 + 5 + .5 (30/60)
= 42 + 5 + .5
= 47.5 hrs

therefore value of

5d 5h 30m = 47.5

Regards,
CalculateDateCount.xls
0
Question by:tia_kamakshi
• 4
• 3
• 3
• +2

LVL 18

Expert Comment

ID: 38779296
I cant see the format you gave in question. can you point out where it is in excel please.
0

Author Comment

ID: 38779408
applogies,
I attached wrong file previously.

Correct file attached.

Regards,
convertDaysToDaysCount.xls
0

LVL 39

Accepted Solution

als315 earned 500 total points
ID: 38779488
This is case when vba function is very usefl (Alt+F11 to look code)
``````Public Function Time2Num(A As String) As Double
Dim d As Integer, h As Integer, m As Integer
Dim s As String, i As Integer, l As Integer
Dim t As String
d = 0
h = 0
m = 0
Time2Num = 0
i = Len(A)
Do While i > 0
t = LCase(Mid(A, i, 1))
If t <> "m" And t <> "h" And t <> "d" Then Exit Do
i = i - 1
s = ""
Do While Mid(A, i, 1) <> " "
s = Mid(A, i, 1) & s
i = i - 1
If i = 0 Then Exit Do
Loop
i = i - 1
If t = "m" Then Time2Num = Time2Num + CInt(s) / 60
If t = "h" Then Time2Num = Time2Num + CInt(s)
If t = "d" Then Time2Num = Time2Num + CInt(s) * 8.4
Loop
End Function
``````
convertDaysToDaysCount.xls
0

Author Closing Comment

ID: 38779536
Many Many Thanks
0

LVL 23

Expert Comment

ID: 38779537
Here might be a formula alternative:

in B3:

=IFERROR(LEFT("  "&A3,FIND("d","  "&A3)-1)*8.4,0)+IFERROR(MID(" "&A3,FIND("h"," "&A3)-2,2),0)+IFERROR(MID(" "&A3,FIND("m"," "&A3)-2,2)/60,0)

copied down
0

LVL 24

Expert Comment

ID: 38779712
you can use just this formula:

=SUM(MID(0&A1&"00000",FIND({"d","h","m"},A1&"xdhm")-1,2)/{0.119048,1,60})
0

LVL 23

Expert Comment

ID: 38779793
As written that formula doesn't work for 3 digit days as per the sample workbook provided.... and it gives #VALUE! error for cells with no days defined...
0

LVL 24

Expert Comment

ID: 38779843
Ah, didn't open the file to see the variants for the conversion...

The following will do all except the 3 digit day... which would take some other work.
=SUM(MID(0&A6&"00000",FIND({"d","h","m"},A6&"xxdhm")-1,2)/{0.119047619047619,1,60})
0

LVL 23

Expert Comment

ID: 38779937
Perhaps with the extra work....?

=SUM(MID("00"&SUBSTITUTE(A3," ","  ")&"00000",FIND({"d","h","m"},SUBSTITUTE(A3," ","  ")&"xxxdhm")-1,3)/{0.119047619047619,1,60})

but, there are many ways to skin it as we proved...
0

LVL 24

Expert Comment

ID: 38780066
Nice:)
0

Author Comment

ID: 38782532
Many Thanks all.

als315 worked for me and created a function.

I learned new thing.

Regards,
0

LVL 23

Expert Comment

ID: 38782605
No problem tia,

We were just giving you (and others with similar issues, that come across this thread) alternatives.

Thanks for the feedback.
0