Are you only applying the code I gave you to the total hours after they have
been calculated? All of your other calculations should be done using the
times formatted as times and when you have the total hours which is formatted
as [hh] then you apply the code I have given you to the total. In the
following sample of data, column D is formatted to [hh]. The code I gave you
works for cell D4 and the answer is 0 Days 2 Hours :-
A B C D
1 Date Start Finish Hours
2 04/02/07 5:00 PM 6:00 PM 01
3 04/03/07 9:00 AM 10:00 AM 01
4 02

Regards,

OssieMac


Regards,

OssieMac


"noname" wrote:

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!