hour differnce, 22/10/06 22.00 to 23/10/06 22.50 in hh:mm

[Guest]

dear sir /mam/sister
how to calculate total hours worked in hh:mm format of an emloyee
between two date or same day in 00:00 hrs format
i used function in field "total hrs"data>control source.
function datediff("h",date1,date2)
that is ("h", 22/10/06 22.00,23/10/06 22.50)
the result should be 24.50 hrs but im getting 25.00hrs
OR
DATEDIFF("N",22/10/0622.00,23/10/06 22.50)/60
NOW ALSO im not getting the correct result kindly replay
your faithfully
balasubramanya
india

 

[Steve Schapel]

Balasubramanya,

A date/time needs to be enclosed with the # delimiter.

When you put 22.50 is this supposed to be 22 hours and 50 minutes, or is
it supposed to be 22½ hours, i.e. 22 hours and 30 minutes? Where I am,
the hours/minutes separator is : but maybe it is . in India?

The DateDiff() function returns whole number. So "h" will not be any
good - you would need to convert from minutes. One way to get what you
want:

DateDiff("n",#22/10/06 22:00#,#23/10/06 22:50#)\60 & ":" &
DateDiff("n",#22/10/06 22:00#,#23/10/06 22:50#) MOD 60

.... but, the result of this is a text string, which could not be
directly used for further calculation such as totals or average or
whatever - is that ok?

 

[Guest]

dear schaoel sir,
i realyy admire ur idea well,but while data entry (of large amount) THE data
base
desighned makes extra work & time consuming for the clerical staff in
putting. enclosing . date with #,
(2) im using the formula function directly in the field data control source
so it will be an direct entry of total hours in the field as wellas yhe
display ,

hence kindly helpme with other way
the hour(function) working well for the time differrence but it is not
recognising the date diff
thanking u
balu

 

[Douglas J. Steele]

The DateDiff function is only capable of returning integer values, therefore
you have no choice but to calculate the difference in minutes and work with
that value.

Since DateDiff("n", ...) is returning an integer value and you're trying to
divide that integer value by another integer, it doesn't surprise me that
your result is an integer. Try using DateDiff("n",22/10/06 22.00, 23/10/06
22.50)/60.0 or CSng(DateDiff("n",22/10/06 22.00, 23/10/06 22.50))/60.0

Now, regarding the use of #, reading between the lines, I'm guessing your
actual code doesn't have literal values in it like that: that you're
actually referring to other text boxes on your form. If that's the case,
wrap the CDate function around each of the references, to ensure that the
input is being treated as dates.