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!

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!

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!

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!

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!

Hi OssieMac,

Thanks for your reply pal.

I think it could be the return value generated by my function which is
of type Double & not as Date, which could be the cause of the problem.
I need to check on that & will inform you..Do you think that could b
the source of the problem ?

Please give me a days time to tell you if it is the source of the
problem....will contact you 12:30 AM IST tonight.

Cheers!



On Apr 9, 9:54 am, OssieMac
wrote:
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!- Hide quoted text -

- Show quoted text -

If your function is returning the hours as a number in hours and not as a
time then simply leave out the line to multiply by 24 because it is already
represented in hours and not as a fraction of a day.

You have me wondering why you are using a function and what it does when
times and dates can all be added and subtracted etc on the spreadsheet.

Regards,

OssieMac

"noname" wrote:

Hi OssieMac,

Thanks for your reply pal.

I think it could be the return value generated by my function which is
of type Double & not as Date, which could be the cause of the problem.
I need to check on that & will inform you..Do you think that could b
the source of the problem ?

Please give me a days time to tell you if it is the source of the
problem....will contact you 12:30 AM IST tonight.

Cheers!



On Apr 9, 9:54 am, OssieMac
wrote:
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!- Hide quoted text -

- Show quoted text -

Hi OssieMac,

To get a better understanding as to what i am trying to do, look at
this post...

http://groups.google.co.in/group/mic...45ccafef80c960


Cheers!


On Apr 10, 3:48 am, OssieMac
wrote:
If your function is returning the hours as a number in hours and not as a
time then simply leave out the line to multiply by 24 because it is already
represented in hours and not as a fraction of a day.

You have me wondering why you are using a function and what it does when
times and dates can all be added and subtracted etc on the spreadsheet.

Regards,

OssieMac

"noname" wrote:
Hi OssieMac,

Thanks for your reply pal.

I think it could be the return value generated by my function which is
of type Double & not as Date, which could be the cause of the problem.
I need to check on that & will inform you..Do you think that could b
the source of the problem ?

Please give me a days time to tell you if it is the source of the
problem....will contact you 12:30 AM IST tonight.

Cheers!

On Apr 9, 9:54 am, OssieMac
wrote:
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!- Hide quoted text -

- Show quoted text -