Convert hours to days-hours

  • Thread starter Thread starter noname
  • Start date Start date
N

noname

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
 
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 said:
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 said:
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!
Click to expand...
Click to expand...
 
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!



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 said:
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,
Click to expand...
OssieMac
Click to expand...

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!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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!



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 said:
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


:

Can anyone help me with a VBA UDF to convert Hours to Days-Hours
format.
Click to expand...
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".
Click to expand...
The Days should be calculated as 9 Hours=1 Days.
Click to expand...
If anyone good in VBA has understood what i am trying to do, i would
appreciate their help!
Click to expand...
Cheers!
Click to expand...
Click to expand...
Click to expand...
 
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!



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 said:
Hi,
Thanks for replying.
Click to expand...
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,
Click to expand...
4/02/07 - 5:00PM to 6:00 PM = 1 hour
&
4/03/07 - 9:00AM to 10:00AM = 1 Hour
Click to expand...
So TOTAL = 2 Hours
Click to expand...
So it should show, "0 Days 2 Hours"
Click to expand...
Which is not what i am getting??
Click to expand...
N.B: if it completes 9 Hours, then it would be considered as 1 Day &
so on so forth.
Click to expand...
Please advice.
Cheers!
Click to expand...

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
:
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
:
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 -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
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 said:
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!



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 said:
Hi,
Thanks for replying.
Click to expand...
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,
Click to expand...
4/02/07 - 5:00PM to 6:00 PM = 1 hour
&
4/03/07 - 9:00AM to 10:00AM = 1 Hour
Click to expand...
So TOTAL = 2 Hours
Click to expand...
So it should show, "0 Days 2 Hours"
Click to expand...
Which is not what i am getting??
Click to expand...
N.B: if it completes 9 Hours, then it would be considered as 1 Day &
so on so forth.
Click to expand...
Please advice.

On Apr 9, 4:12 am, OssieMac <[email protected]>
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.
Click to expand...
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,
Click to expand...
OssieMac
Click to expand...

:
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,
Click to expand...
OssieMac
Click to expand...

Hi,
Click to expand...

Can anyone help me with a VBA UDF to convert Hours to Days-Hours
format.
Click to expand...
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".
Click to expand...
The Days should be calculated as 9 Hours=1 Days.
Click to expand...
If anyone good in VBA has understood what i am trying to do, i would
appreciate their help!
Click to expand...
Cheers!- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
Click to expand...
 
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/mi...acfb5/7a45ccafef80c960?hl=en#7a45ccafef80c960


Cheers!


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 said:
Hi OssieMac,
Click to expand...
Thanks for your reply pal.
Click to expand...
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 ?
Click to expand...
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!
Click to expand...

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
:
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 <[email protected]>
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
:
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
:
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 -
Click to expand...
Click to expand...
Click to expand...
 
Back
Top