Link to home
Start Free TrialLog in
Avatar of andysalih
andysalih

asked on

time problem

Doesn anybody know how to calculate a time difference,

when i write some code to get the result of say (9pm - 8am) it keeps adding 12 hours on.

but if i do it from 1am - 6am then it works the time out correct.

can somebody show me some code that gives me the hours difference from 8pm - 6am.

cheers
Andy
Avatar of glass_cookie
glass_cookie

Hi!

You could use the datediff function to do that.  Here's the extract from my help file (use 'h'):


Returns a Variant (Long) specifying the number of time intervals between two specified dates.

Syntax

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

The DateDiff function syntax has these named arguments:

Part     Description
interval     Required. String expression that is the interval of time you use to calculate the difference between date1 and date2.
date1, date2     Required; Variant (Date). Two dates you want to use in the calculation.
firstdayofweek     Optional. A constant that specifies the first day of the week. If not specified, Sunday is assumed.
firstweekofyear     Optional. A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.
Settings

The interval argument has these settings:

Setting     Description
yyyy     Year
q     Quarter
m     Month
y     Day of year
d     Day
w     Weekday
ww     Week
h     Hour
n     Minute
s     Second

That's it!

glass cookie : )
hi andysalih,

Try this,
X = Abs(DateDiff("H", "21:00:00", "8:00:00"))

where the time is in 24hr format

hope this helps!

-priya
Oops - now I get what you mean.  Since the datediff function also gives you the same problem, how about reversing the time?  As in...

Instead of:

MsgBox DateDiff("h", "8:00 PM", "6:00 AM")

You do something like:

MsgBox DateDiff("h", "6:00 PM", "8:00 AM")

Of course, you'll need some coding to determine is the switching is required by using the instr function to check for 'AM' or 'PM'

That's it!

glass cookie ; )

therefore X would return "13"

Hello,

if the two 'times' are not within the same day you've got to use the date to tell the DateDiff function what to do

DateDiff("H", "01/01/2002 21:00:00", "01/02/2002 8:00:00")
Avatar of andysalih

ASKER

i dont understand the calcs here

in my world 9pm - 8am in the morning is 11 hours, How can it be 13.

why is it returning the value 13


cheers

Andy
ASKER CERTIFIED SOLUTION
Avatar of priya_pbk
priya_pbk

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
i dont understand the calcs here

in my world 9pm - 8am in the morning is 11 hours, How can it be 13.

why is it returning the value 13


cheers

Andy
oops. REFRESHED by mistake sorry guys

'Formatted:

Private Sub Command1_click()

MsgBox DateDiff("H", Format("27/07/2002 21:00:00", "dd-mm-yyyy hh:mm:ss"), Format("28/07/2002 08:00:00",
"dd-mm-yyyy hh:mm:ss"))

'the above returns 11

MsgBox DateDiff("H", Format("27/07/2002 20:00:00", "dd-mm-yyyy hh:mm:ss"), Format("28/07/2002 06:00:00", "dd-mm-yyyy hh:mm:ss"))

'this returns 10

End Sub


-priya

thats the one,

cheers
Andy