VBA: Adding Times/Decimals Together in Textboxes

[katk]

Hi all,

I have a series of textboxes in a userform that need to be added up... the answer goes into a final textbox, which can be altered if necessary. The basic idea is "end time - start time - lunch time + travel time = total."

I was using this code to achieve it, which worked fine when all the textboxes contained straight-up numbers (eg 9.5):

Dim dblSum As Double
      On Error Resume Next
      dblSum = CDbl(Start1.Value)
      dblSum = (dblSum) * (-1) + CDbl(End1.Value)
      dblSum = dblSum - CDbl(Lunch1.Value)
      dblSum = dblSum + CDbl(Travel1.Value)
        Total1 = dblSum

The entry needs to be very intuitive however, since I'm making this for someone else who isn't very computer-savvy. the Start time and End time need to be written like "9:30 a" or "10:00 p" while the lunch and travel times will be decimals (.5, 1.25)...

I can't figure out how to get those different kinds of values to add and subtract within this basic code framework... mostly because I don't actually know what the "double" is doing... I tried taking it away and just adding the values (with the decimals divided by 24) but that didn't work, and I don't know what else to convert to/format as to make this work.

This might be easy... but I couldn't find a solution online that combined the issue of the textboxes/vba with the issue of adding disparate formats... and although I'm a decent coder, I've just never been able to understand dim'ing things as double, single, long.

As a side note, I know that "on error resume next" isn't a great tactic... but I'll deal with that later!

Thanks so much for any help,
Katherine

 

[JoeMo]

Maybe something like this to convert the End-Start time to a decimal value in hours:

Format((Cdate(end time)-Cdate(start time)*24,"0.00")

 

[katk]

Okay, that definitely helped! I stopped getting errors immediately, and then I added an additional *24 and that seemed to get me the right answers... the only problem is, for parts of hours (eg 8:15 - 9:30 as opposed to 8:00 to 9:00), it's taking them out of 100 instead of out of 60... so it thinks 15 minutes is 1/6th rather than 1/4.

(In other words, it knows 8:00 a to 9:00 p = 13 hours, but it thinks 8:00 a to 9:15 p is 13.166666667 hours...)

This is what my code looks like right now:

Dim dblSum As Double
Dim edst as Date 'end time - start time
edst = Format((CDate(End1.Value)-CDate(Start1.Value))*24,"0.00") 
On Error Resume Next      
dblSum = edst * 24       
dblSum = dblSum - CDbl(Lunch1.Value)      
dblSum = dblSum + CDbl(Travel1.Value)        
Total1 = dblSum

If you have any ideas on how I can fix the minutes... that would be great! Thanks so much!

 

[JoeMo]

Select two cells adjacent to each other. In the left-most cell enter 9:15 PM, in the next cell to the right enter 8:00 AM. Select the cell containing 9:15 PM. In the VB editor Immediate Window (Open editor using alt + F11, then Immediate window using ctrl+G) enter
?Format((Cdate(selection)-Cdate(selection.offset(0,1)))*24,"00.00")
and hit return. I get 13.25.What do you get?

 

[katk]

I'm getting 13.25 too... however when I put the same numbers in the userform I get (correction, misread this the first time because I was guesstimating fractions) 13.41666667. So I guess somehow it's dividing by 36, rather than 60...
Could it have something to do with the double? Thanks for working with me on this, I really appreciate it!

 

[JoeMo]

I'm getting 13.25 too... however when I put the same numbers in the userform I get (correction, misread this the first time because I was guesstimating fractions) 13.41666667. So I guess somehow it's dividing by 36, rather than 60...
Could it have something to do with the double? Thanks for working with me on this, I really appreciate it!

If you are getting the correct value for edst (13.25 in your example) in hours, why are you multiplying it by 24 to get dblSum?

 

[katk]

When i did your test on the worksheet with the immediate window, i got 13.25... But with my code, the numbers come out as tubby decimals unless i multiply that extra 24.

 

[JoeMo]

When i did your test on the worksheet with the immediate window, i got 13.25... But with my code, the numbers come out as tubby decimals unless i multiply that extra 24.

I just discovered from your earlier post that you have dimensioned edst as Date.
This line:
edst = Format((CDate(End1.Value)-CDate(Start1.Value))*24,"0.00")
is intended to coerce edst to a decimal value which conflicts with the dimensioning. Try dimensioning edst as double and remove that *24 from the dblSum assignment.

 

[katk]

I just discovered from your earlier post that you have dimensioned edst as Date.
This line:
edst = Format((CDate(End1.Value)-CDate(Start1.Value))*24,"0.00")
is intended to coerce edst to a decimal value which conflicts with the dimensioning. Try dimensioning edst as double and remove that *24 from the dblSum assignment.

Thanks so much, that worked! I knew the double issue had to be involved... Interestingly, it also worked for me (before i got this post) to remove the second * 24 and add a decimal place to the format (0.000)...but i think your fix is more logical and therefore better! Again, thank you so much!