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.ValueEnd SubPrivate Sub Command2_Click() Text2.Text = DTPicker1.ValueEnd SubPrivate Sub Text1_Change() If IsDate(Text2) And IsDate(Text1) Then Text3 = CDate(Text2) - CDate(Text1) End IfEnd SubPrivate Sub Text2_Change() If IsDate(Text2) And IsDate(Text1) Then Text3 = CDate(Text2) - CDate(Text1) End IfEnd Sub

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.

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 SelectEnd Function

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

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.

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...

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

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Introduction
In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function. While writing that article I realized that no oâ€¦

As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). Uâ€¦

This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code.
This lâ€¦