Solved

format time to hours

Posted on 2014-04-24
14
236 Views
Last Modified: 2014-04-28
When I run the following code, my output for Text3 looks like this -0.933333333

I want it to be in hours.  How do I format it to accomplish this?


Private Sub Command1_Click()
    Text1.Text = DTPicker1.Value
End Sub
Private Sub Command2_Click()
    Text2.Text = DTPicker1.Value
End Sub

Private Sub Text1_Change()
    If IsDate(Text2) And IsDate(Text1) Then
        Text3 = CDate(Text2) - CDate(Text1)
    End If
End Sub

Private Sub Text2_Change()
    If IsDate(Text2) And IsDate(Text1) Then
        Text3 = CDate(Text2) - CDate(Text1)
    End If
End Sub

Open in new window

0
Comment
Question by:al4629740
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 40020492
You should be able to use the FORMAT function, something like this:

Private Sub Command1_Click()
    Text1.Text = Format(DTPicker1.Value, "h")
End Sub

Typically, this is used for formatting time like this:

format(SomeTimeOrDateVariable, "hh:mm:ss")

---
Alternately, you can simply multiply the result by 24 to convert from .9333 days into hours (then truncate or round as needed)
0
 

Author Comment

by:al4629740
ID: 40020544
How do I have up to two decimal spaces?
0
 
LVL 1

Expert Comment

by:dwe0608
ID: 40020870
do you want to show Hours:mm?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40020946
Take the date difference and multiply by 24.  You can round or truncate as needed.
Example:
         Text3 = (CDate(Text2) - CDate(Text1)) * 24

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 40020948
I failed to see that the 24 multiplier has already been suggested in a prior comment.
http:#a40020492
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 40021339
So maybe you want to have something like this to get exactly 2 decimal positions:

Private Sub Text2_Change()
    If IsDate(Text2) And IsDate(Text1) Then
        Text3 = Format((CDate(Text2) - CDate(Text1)) * 24, "##0.00;-##0.00")
    End If
End Sub

Open in new window


The format will calculate the difference and show it with up to 3 positions to the left of the decimal and exactly 2 to the right of the decimal.  If the number is negative, it shows a negative sign up front.

If you want less than 2 decimal positions, you can replace ##0.00 with something like ##0.0# or even ##0.## (for both positions of the quoted section of the Format command.

FYI
The format command works something like this:

Format(item_to_format, [format_text])

The format_text is optional but if included for numbers the first part is for positive numbers then a semicolon and the second part is for negative numbers.

Hope that helps.
0
 

Author Comment

by:al4629740
ID: 40023731
I get a syntax error

Text3 = Format((CDate(Text2) - CDate(Text1)) * 24, "##0.00;-##0.00")
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 1

Expert Comment

by:dwe0608
ID: 40025608
if you are looking for a function to make your text formatted: hh:mm then the following function will achieve that :

Function szFormatTime(iTime As Double, Optional incoming As Integer = 2, Optional fmt As Integer = 1)
 ' the incoming iTime variable holds the number of seconds
  Dim itt, hours, minutes, seconds, millisec
 
  Select Case incoming
   Case 1 ' we treat itime as minutes and convert it to seconds
    iTime = iTime * 60
   Case 2 'we treat itime as seconds
    ' fall through
   
  End Select
 
  itt = iTime
  hours = CCur(Format((itt \ 3600), "00"))
  minutes = CCur(Format((itt \ 60) Mod 60, "00"))
  seconds = CCur(Format((itt \ 1) Mod 60, "00"))
  millisec = CCur(Format((itt Mod 1000) \ 10, "00"))
  
  Select Case fmt
    Case 1
        szFormatTime = hours & ":" & minutes '& ":" & seconds & ":" & millisec
        'returns a format of 6:08
    Case 2
        szFormatTime = hours & "h " & minutes & "m" ' & seconds & ":" & millisec
        ' returns a format 6h8m
    Case Else
        szFormatTime = 0
  End Select
  
End Function

Open in new window


To call the function using your existing code example, you would do it like this:

Text3.text = szFormatTime(DateDiff("s", CDate(text1.text), CDate(Text2.Text)), 2,1)


HTH

DWE
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 total points
ID: 40026258
Regarding your sytnax error...what gets highlighted at that time?
You may want to prefix the word format with VBA.format

you may want to use the debugger to verify the various parts of the formula to see where it's going wrong, like break it up as follows:
 
Text3 = Format((CDate(Text2) - CDate(Text1)) * 24, "##0.00;-##0.00")

Open in new window


changes to:
Dim dt1 As Date
Dim dt2 As Date
Dim dtDiff As Double
Dim dtHours As Double

dt1 = CDate(Text1.Value)
dt2 = CDate(Text2.Value)
dtDiff = (dt2 - dt1)
dtHours = dtDiff * 24
Text3 = Format(dtHours, "##0.00;-##0.00")

Open in new window

0
 
LVL 1

Expert Comment

by:dwe0608
ID: 40026340
Hi guys,

Not wanting to be a spoil sport, the but the answer accepted seems to be wrong and returns an incorrect calculation - if you're using these calculations to calculate charges then those calculations will be wrong on the basis that you are not actually returning hh:mm

Using the attached file, you can see the difference.
HoursTest.zip
0
 

Author Comment

by:al4629740
ID: 40026427
to calculate "charges" ?

I don't understand.  Can you explain?
0
 
LVL 1

Expert Comment

by:dwe0608
ID: 40026491
hi

you're doing an equation on the time difference between two dates/times - you want the difference to be shown in a format of hours:minutes ... I have assumed that you want to show that to reflect the time used for a particular purpose ...

If you download and use the sample project I uploaded ... look in the form_load event and you will see how I've done the calculations - I've used seconds to add to the date in text1 - 3660 seconds is equal to 1 hour 1 minute.

You will see for a time difference of 1 hour 1 minute - the calculation you have been provided returns an answer of 1.2 - my function returns 1.1

If you are using the calculations in a time billing environment or other environment where the calculations are used to either add up the time or apply that time in the calculation of charges in a time billing environment, then the calculation in the answer you have accepted will be incorrect and either result in an incorrect time calculation and or charges.


HTH

DWE
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 40027487
dwe, I'm not sure where you decided that the output should be in time format.  Author wrote this:
How do I have up to two decimal spaces?

It's unlikely that you will use decimal positions in time format (unless you're going to milliseconds) which is why I show the number formatted as integer plus two decimal positions.
For example, instead of 3:30, it would show 3.50 hours.

And in my tests, all numbers round, which may not have been the expected result, but that was never addressed in the question.  For example, 1 minute shows up as 0.02 but it's actually 0.0166666666...
0
 
LVL 1

Expert Comment

by:dwe0608
ID: 40028143
rspahitz, the querist's original question was "I want it to be in hours" and then went on the say about the decimal places ... I accept what you say,  he doesnt explicitly say "time format" ... I just didnt know if he understood that he wasnt getting a time formatted answer ...

in this question the querist asks about the DTPicker and time format, one can only assume this question is related and of course if I am correct in that assumption, then clearly a time formatted answer is appropriate

Nevertheless, al4629740 can see by our comments there is a difference in the calculations and he can implement which ever if appropriate.

Regards

DWE
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now