Date/Time difference

[mtagliaferri]

I have been stuck for a while, with this problem and still I have not solved it.
How can I get the difference between 2 dates and time, example:
DateStart TimeStart DateEnd TimeEnd
01/01/07 12.00 03/01/07 13.20
20/01/07 07.12 23/01/07 08.27

I need to return the result in "Hours" for the first record 49.20 hour and 73.15 for the second record.
How can I achieve it?

 

[Zigzag]

Hi,

Start off by adding 2 new text boxes to you form.
These text boxes (datetimestart & datetimeend)should have the control source as

=[datestart] & " " & [timestart]
=[dateEnd] & " " & [timeEnd]

This will concatanate the date and time together.

Now you can easily use datediff to get the hours between the two.

=DateDiff("h",[DateTimeStart],[DateTimeEnd],0,0)


Any problems let me know.

Garry

 

[mtagliaferri]

Hi Garry,
thanks I got it to work! but I also need to show the minutes too (49.20), I have tried to change the code in

=DateDiff("h","m",[DateTimeStart],[DateTimeEnd],0,0)

but does not seam to work!!!
I then need to add a second field wich it rounds up to the next quarter of hous, for example if Hours = 49.13 it has to be rounded up to 49.15, if the result is 49.17 rounded up to 49.30 and so on...

Thanks
Mark

 

[Zigzag]

Hi Mark,

Calculate your hours, then calculate you minutes.

Hours=DateDiff("h",[DateTimeStart],[DateTimeEnd],0,0)
Mins=DateDiff("n",[DateTimeStart],[DateTimeEnd],0,0)

then you need to take your hours * 60. and take this value away from Mins

ie
Mins =(Mins - (Hours * 60))

Now you should have Hour and remaining minutes

Once you get this working let me know and we can then have a crack art rounding things up.

Garry

 

[mtagliaferri]

Iam confused, I have added a text box named mins and placed the formula

=DateDiff("n",[DateTimeStart],[DateTimeEnd],0,0)

but it will not return me the minutes at all
I have also tried to put both codes in the Hours but that worked out as a total disaster!

 

[Zigzag]

Hi Mark,

As you have posted should work.
=DateDiff("n",[DateTimeStart],[DateTimeEnd],0,0)

What does it give you? nothing> error#?
Let me know.

Garry

 

[ajetrumpet]

Mtag,

Here's another idea to add to the mix...

Put this into the CS of your txtbox, and see what happens...

CLng(DateDiff("h",[datestart],[dateend])) + 

     Abs(DateDiff("n",[timestart],[timeend])/60)

 

[mtagliaferri]

Garry, it does work!!!
I have wrote the code with "m" for minutes instead of "n" I have now combined the result into a text box as TOT, I just need to round it up now

Mark

 

[mtagliaferri]

Garry, it does work!!!
I have wrote the code with "m" for minutes instead of "n" I have now combined the result into a text box as TOT, I just need to round it up now

Mark

 

[Zigzag]

Mark,

This in a control source will round you mins down to the ladt 15mins

=Int((DateDiff("n",[DateTimeStart],[DateTimeEnd],0,0)-([Hours]*60))/15)*15

Garry

 

[mtagliaferri]

Garry, thank you for your patience, but I need toround the value up, for example
for "n" values between 01 and 14 has to return 15, vor "n" values between 16 and 29 = 30, for values from 31 to 44 = 45 and finally for the values between 46 and 59 has to return 00 minutes but has to add 1 hour to "h"

 

[Zigzag]

Hi Mark,

To achieve what you are looking for will probably need a little VBA coding, I dont have the time at the moment but might find time to play with it tonight or tomorrow. You might be better off starting a new thread asking for assistance with "Round up Time"

Garry

 

[mtagliaferri]

Thanks Garry,
I will surf the threads, but Iwould really apprecaite if you can look into it

Thanks again

Mark

 

[Rabbie]

Try this

=Int(((DateDiff("n",[DateTimeStart],[DateTimeEnd],0,0)-([Hours]*60))+15)/15)*15


HTH

 

[mtagliaferri]

Thanks for the suggestion, I have tried it put vor values of 46 > returns 60 and at that stage I need an extra hour to be added to the hours and the rounded up value returns to 00 minutes.

 

[mtagliaferri]

I have know something bizarre happening; I have applied all the formulas in my proper database it returns me a negative number on my minutes, example
Start = 1330 End = 14.25 the result in the minutes field is -05
How can this be????