Multiplying with DateDiff result

S

Sketchin

Registered User.

Local time

Today, 11:03

Joined

Dec 20, 2011

Messages

577

• Sep 18, 2012

• #1

I am using datediff to calculate how many hours are between 2 times and getting a result like 7:30.

Is there a way to use that result in a calculation to figure out how many hours occured in so many days?

This is my current expression:
TrainingManHours: (weekdays([TrainingStartDate],[TrainingEndDate]))*(DateDiff("h",[StartTime],[EndTime]))

Btw..weekdays is just a function that tells me how many weekdays are between 2 dates.

 

pr2-eugin

Super Moderator

Local time

Today, 19:03

Joined

Nov 30, 2011

Messages

8,494

• Sep 18, 2012

• #2

I thought it would return the integer.. I just used the following in the immediate window to double check it gave the right answer..

Code:

? DateDiff("h",#18/09/2012 09:14:23 AM#,Now)
 7

 

A

AccessMSSQL

Seasoned Programmer-ette

Local time

Today, 11:03

Joined

May 3, 2012

Messages

636

• Sep 18, 2012

• #3

Can you just cast both sides of that equation to integer and then you won't get the 7:30 result. What type is your weekdays function returning?

 

S

Sketchin

Registered User.

Local time

Today, 11:03

Joined

Dec 20, 2011

Messages

577

• Sep 18, 2012

• #4

I must've confused myself because I did a test with Format, and it returned 7:30

test: Format([StartTime]-1-[EndTime],"Short Time")

Will look into this further, thanks for your time.

(Weekdays returns an integer)

 

S

Sketchin

Registered User.

Local time

Today, 11:03

Joined

Dec 20, 2011

Messages

577

• Sep 18, 2012

• #5

Ok, my problem was if I do
(DateDiff("h",[StartTime],[EndTime]))
and my start time is 8:30 and end time is 4:00, the result returned is 8, not 7.5.

I assume this is because of my data type being integer rather than ...double?

 

pr2-eugin

Super Moderator

Local time

Today, 19:03

Joined

Nov 30, 2011

Messages

8,494

• Sep 18, 2012

• #6

Even if the variable is double you will still get that same value, because the value returned by DateDiff function is an Integer.

 

S

Sketchin

Registered User.

Local time

Today, 11:03

Joined

Dec 20, 2011

Messages

577

• Sep 18, 2012

• #7

The solution was to do this:

test: (([StartTime]-[EndTime])*-24)*(weekdays([TrainingStartDate],[TrainingEndDate]))