Hi,
Thanks for replying.

If i have say 9 (format [hh]) showing as hours in cell E15 & i check
it, i get 1 day 0 hours. However since my shift time is from
9:00AM-6:00PM (9 hours) & if on Monday 4/02/07, i worked from 5:00PM
to next day 4/03/07, 10:00AM, it should be counted as,

4/02/07 - 5:00PM to 6:00 PM = 1 hour
&
4/03/07 - 9:00AM to 10:00AM = 1 Hour

So TOTAL = 2 Hours

So it should show, "0 Days 2 Hours"

Which is not what i am getting??

N.B: if it completes 9 Hours, then it would be considered as 1 Day &
so on so forth.

Please advice.

Cheers!



On Apr 9, 4:12 am, OssieMac
wrote:
Sorry I was having a bad moment. The previous post will not work for a value
formatted as hours. Try this instead because a time is actually a fraction of
a day and needs to be multiplied by 24 to convert it to a numeric number of
hours instead of a fraction of a day.

Sub Test_Days_Hours()
Dim origHrs As Date
Dim hrsNumeric As Long
Dim hrs As Long
Dim dys As Long
Dim dysHrs As String
origHrs = Range("A1") 'Range A1 contains 25:00 formatted as [h]
hrsNumeric = origHrs * 24
dys = Int(hrsNumeric / 9)
hrs = hrsNumeric - dys * 9
dysHrs = dys & " Days " & hrs & " Hours"
MsgBox dysHrs
End Sub

Regards,

OssieMac

"OssieMac" wrote:
See if this will do what you want.
Sub Test_Days_Hours()
Dim dysHrs
Dim hrs
Dim dys
dysHrs = 25
dys = Int(25 / 9)
hrs = dysHrs - dys * 9
dysHrs = dys & " Days " & hrs & " Hours"
MsgBox dysHrs
End Sub

Regards,

OssieMac

"noname" wrote:

Hi,

Can anyone help me with a VBA UDF to convert Hours to Days-Hours
format.

I have a UDF function which returns value in Hours e.g. 25 formatted
as [hh] & i want to change it, or supply that return value to another
UDF function that will return a value in Days-Hours format e.g. "3
Days-4 Hours".

The Days should be calculated as 9 Hours=1 Days.

If anyone good in VBA has understood what i am trying to do, i would
appreciate their help!

Cheers!