• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

Calculate no of hrs

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

Please help me creating the formula

Please find attached the excel

Regards,
CalculateDateCount.xls
0
tia_kamakshi
Asked:
tia_kamakshi
  • 4
  • 3
  • 3
  • +2
1 Solution
 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
I cant see the format you gave in question. can you point out where it is in excel please.
0
 
tia_kamakshiAuthor Commented:
applogies,
I attached wrong file previously.

Correct file attached.

Regards,
convertDaysToDaysCount.xls
0
 
als315Commented:
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

Open in new window

convertDaysToDaysCount.xls
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
tia_kamakshiAuthor Commented:
Many Many Thanks
0
 
NBVCCommented:
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
 
SteveCommented:
you can use just this formula:

=SUM(MID(0&A1&"00000",FIND({"d","h","m"},A1&"xdhm")-1,2)/{0.119048,1,60})
0
 
NBVCCommented:
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
 
SteveCommented:
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
 
NBVCCommented:
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
 
SteveCommented:
Looks about right NB :)
Nice:)
0
 
tia_kamakshiAuthor Commented:
Many Thanks all.

als315 worked for me and created a function.

I learned new thing.

Regards,
0
 
NBVCCommented:
No problem tia,

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

Thanks for the feedback.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now