I am trying to perform a calculation that firstly calculates the difference between 2 fields containing time values, then converts this figure to a number, finally this number is multiplied by a value in a 3rd field. The fields are: StartTime, EndTime, TotalStaff and the result is placed in a Result field.
My sample values are:
StartTime = 16:45
EndTime = 19:00
TotalStaff = 3
Result = 6.75

My code so far is:
Result = DateDiff("h", CDate(StartTime.Value),CDate(EndTime.Value) * Val(TotalStaff.Value))

My result using the above code returns: 1811969 which is completely wrong.

Any idea what I am doing wrong, and what I need to do to my code to get it to work?
Also, I have code in a section which performs other stuff (basically saving the form data to Excel cells) and I wonder if these operations are somehow buggering up the calculation?

Any help or guidance would be greatly appreciated.  

The problem you have is that the datediff function is expecting a date and time, not just a time.

Can you precede the time with a date.

eg

"12/06/03 12:43"

Cheers Steve

Then I tried this

    MsgBox DateDiff("h", "12:00", "14:00")

This did return 2. Maybe you need to remove the cdate

[Edited by steve_w on 12-06-2003 at 12:34 PM GMT]

I think you just need to check your parenthesis...

Result = DateDiff("h", CDate(StartTime.Value),CDate(EndTime.Value)) * Val(TotalStaff.Value)

____________________________
Eggheads unite! You have nothing to lose but your yolks.

I wish my maths was better too  

LMAO!

____________________________
Eggheads unite! You have nothing to lose but your yolks.

This is Midgelo who posted the original thread (forgot password as now on home PC, so created new temp log in)

Result = DateDiff("n", CDate(StartTime.Value),CDate(EndTime.Value)) / 60 * Val(TotalStaff.Value)

The above code works perfectly if my time values start and end on different days i.e:
StartTime = 23:00 (07/06/03)
EndTime = 01:00   (08/06/03)
TotalStaff = 3
This gives the correct result of 6

But...using the same code, when the start and end times are for the same day i.e.
StartTime = 01:00
EndTime = 02:00
TotalStaff = 1
the result is = 25

It reads the times correctly, but appears to add 24 for every TotalStaff value i.e.
if TotalStaff value is 1, the Result is 25, 2 would be 50, 3 would be 75 and so on.

Any idea how I get around this? I thought maybe an if then statement, but dont know how to deduct 24 from total staff for every number increase in totalStaff.

did you post your code exactly as you have it in your app?

dunno...cause when I try this...

start = #6/8/2003 1:00:00 AM# fin = #6/8/2003 2:00:00 AM# staff = 1 Result = DateDiff("n", CDate(start), CDate(fin))/60*Val(staff) MsgBox "Result: " & Result

I get the correct result which is 1...
or...60/60*1 = 1

quote:

---

but dont know how to deduct 24 from total staff for every number increase in totalStaff.

---

Only just seen your message, and will try it soon as I can and get back to you.

The following 2 codes work when run seperately i.e. the first one does the 'same day' calculations by adding 1440 (24 hours in minutes) the second part after the else statement does the 2 day part. Only trouble now is put in this if else statement only one part of the code is executed. Any idea what the solution is?

If StartTime.Value > EndTime.Value Then

Result = DateDiff("n", CDate(StartTime.Value),CDate(EndTime.Value) - 1440) / 60 * Val(TotalStaff.Value)

Else

Result = DateDiff("n", CDate(StartTime.Value),CDate(EndTime.Value)) / 60 * Val(TotalStaff.Value)

End If

quote:

---

Only trouble now is put in this if else statement only one part of the code is executed. Any idea what the solution is?

---