How to calculate a difference between Dates in hours & minutes?

[Guest]

How to calculate a difference between Dates in hours & minutes?
There are [Date&Time Start], [Date&Time Off] and [WorkTime]. How to
calculate a difference between [Date&Time Start] and [Date&Time Off] in
hours & minutes in [WorkTime]?

 

[Guest]

Hi

On a form set the text box [WorkTime] source as
=(DateDiff("n",[Date&Time Start],[Date&Time Off]))\60 &
Format((DateDiff("n",[Date&Time Start],[Date&Time Off])) Mod 60,"\:00")

In a query
WorkTimeDateDiff("n",[Date&Time Start],[Date&Time Off]))\60 &
Format((DateDiff("n",[Date&Time Start],[Date&Time Off])) Mod 60,"\:00")

 

[Guest]

It doesn't work for TIME but for DATE only. As result
between 23/12/07 23:33 and 25/12/07 22:40 is 29/12/1899

dos1970
Sharjah

"Wayne-I-M" пишет:

Hi

On a form set the text box [WorkTime] source as
=(DateDiff("n",[Date&Time Start],[Date&Time Off]))\60 &
Format((DateDiff("n",[Date&Time Start],[Date&Time Off])) Mod 60,"\:00")

In a query
WorkTimeDateDiff("n",[Date&Time Start],[Date&Time Off]))\60 &
Format((DateDiff("n",[Date&Time Start],[Date&Time Off])) Mod 60,"\:00")



--
Wayne
Manchester, England.

How to calculate a difference between Dates in hours & minutes?
There are [Date&Time Start], [Date&Time Off] and [WorkTime]. How to
calculate a difference between [Date&Time Start] and [Date&Time Off] in
hours & minutes in [WorkTime]?

 

[Guest]

The following function returns the time difference in the format h:nn:ss

Public Function TimeDuration(dtmFrom As Date, dtmTo As Date) As String

Const HOURSINDAY = 24
Dim strDays As String
Dim strMinutesSeconds As String
Dim dblDuration As Double

dblDuration = dtmTo - dtmFrom

'get number of days
strDays = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

TimeDuration = strDays & strMinutesSeconds

End Function

Call it like so:

=TimeDuration([Date&Time Start], [Date&Time Off])

Ken Sheridan
Stafford, England

 

[Guest]

What is the format of [WorkTime]
Is it set to hh:mm

--
Wayne
Manchester, England.

It doesn't work for TIME but for DATE only. As result
between 23/12/07 23:33 and 25/12/07 22:40 is 29/12/1899

dos1970
Sharjah

"Wayne-I-M" пишет:

Hi

On a form set the text box [WorkTime] source as
=(DateDiff("n",[Date&Time Start],[Date&Time Off]))\60 &
Format((DateDiff("n",[Date&Time Start],[Date&Time Off])) Mod 60,"\:00")

In a query
WorkTimeDateDiff("n",[Date&Time Start],[Date&Time Off]))\60 &
Format((DateDiff("n",[Date&Time Start],[Date&Time Off])) Mod 60,"\:00")



--
Wayne
Manchester, England.

How to calculate a difference between Dates in hours & minutes?
There are [Date&Time Start], [Date&Time Off] and [WorkTime]. How to
calculate a difference between [Date&Time Start] and [Date&Time Off] in
hours & minutes in [WorkTime]?

 

[Al Campagna]

Dos,
=Int(DateDiff("n",[Start],[End])/60) & "Hours " & DateDiff("n",[Start],[End]) Mod 60 &
"Mins"
should do it...
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

Looking good but return #Name?

Becouse format of a DATE is dd/mm/yy or some thing else but [Date&Time
Start]&[Date&Time Off] format is "dd/mm/yy, hh:nn"

SOS

dos1970





"Ken Sheridan" пишет:

The following function returns the time difference in the format h:nn:ss

Public Function TimeDuration(dtmFrom As Date, dtmTo As Date) As String

Const HOURSINDAY = 24
Dim strDays As String
Dim strMinutesSeconds As String
Dim dblDuration As Double

dblDuration = dtmTo - dtmFrom

'get number of days
strDays = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

TimeDuration = strDays & strMinutesSeconds

End Function

Call it like so:

=TimeDuration([Date&Time Start], [Date&Time Off])

Ken Sheridan
Stafford, England

How to calculate a difference between Dates in hours & minutes?
There are [Date&Time Start], [Date&Time Off] and [WorkTime]. How to
calculate a difference between [Date&Time Start] and [Date&Time Off] in
hours & minutes in [WorkTime]?

 

[Al Campagna]

dos1970,
A #Name error means that one (or more) of the elements in your calculation is not
recognized. Your object names may be mispelled, or contain/don't contain a space, etc...
Always use the Name of the control, not the ControlSource field name.
Also, I'd avoid the & and spaces in any object names. Try DateTimeOff, or
DateTimeStart instead.

It would help if you posted the code you have now. Cut & Paste it into your posts, so
it's exactly the same as your failing code, and... there's no chance of introducing other
errors in the transcription.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Looking good but return #Name?

Becouse format of a DATE is dd/mm/yy or some thing else but [Date&Time
Start]&[Date&Time Off] format is "dd/mm/yy, hh:nn"

SOS

dos1970





"Ken Sheridan" ?????:

The following function returns the time difference in the format h:nn:ss

Public Function TimeDuration(dtmFrom As Date, dtmTo As Date) As String

Const HOURSINDAY = 24
Dim strDays As String
Dim strMinutesSeconds As String
Dim dblDuration As Double

dblDuration = dtmTo - dtmFrom

'get number of days
strDays = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

TimeDuration = strDays & strMinutesSeconds

End Function

Call it like so:

=TimeDuration([Date&Time Start], [Date&Time Off])

Ken Sheridan
Stafford, England

How to calculate a difference between Dates in hours & minutes?
There are [Date&Time Start], [Date&Time Off] and [WorkTime]. How to
calculate a difference between [Date&Time Start] and [Date&Time Off] in
hours & minutes in [WorkTime]?

 

[Guest]

The format should not be relevant. Date/time values are implemented as a 64
bit floating point number as an offset from 30 December 1899 00:00:00. The
format is merely how the underlying value is presented. Nor does the
inclusion of a time of day make any difference. All date time values include
a time of day, even if it is zero (corresponding to midnight at the start of
the day). Similarly a time value independent of a date cannot not exist. If
a time only is entered into a date/time data type then it is actually that
time on 30 December 1899.

Where have you put the function? It should be in a standard module, and the
module should be saved with a name other than TimeDuration.

Ken Sheridan
Stafford, England

Looking good but return #Name?

Becouse format of a DATE is dd/mm/yy or some thing else but [Date&Time
Start]&[Date&Time Off] format is "dd/mm/yy, hh:nn"

SOS

dos1970





"Ken Sheridan" пишет:

The following function returns the time difference in the format h:nn:ss

Public Function TimeDuration(dtmFrom As Date, dtmTo As Date) As String

Const HOURSINDAY = 24
Dim strDays As String
Dim strMinutesSeconds As String
Dim dblDuration As Double

dblDuration = dtmTo - dtmFrom

'get number of days
strDays = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

TimeDuration = strDays & strMinutesSeconds

End Function

Call it like so:

=TimeDuration([Date&Time Start], [Date&Time Off])

Ken Sheridan
Stafford, England

How to calculate a difference between Dates in hours & minutes?
There are [Date&Time Start], [Date&Time Off] and [WorkTime]. How to
calculate a difference between [Date&Time Start] and [Date&Time Off] in
hours & minutes in [WorkTime]?

 

[Guest]

Thanks a lot!!!

WORKS!!!


"Al Campagna" пишет:

Dos,
=Int(DateDiff("n",[Start],[End])/60) & "Hours " & DateDiff("n",[Start],[End]) Mod 60 &
"Mins"
should do it...
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

How to calculate a difference between Dates in hours & minutes?
There are [Date&Time Start], [Date&Time Off] and [WorkTime]. How to
calculate a difference between [Date&Time Start] and [Date&Time Off] in
hours & minutes in [WorkTime]?

 

[Guest]

Thanks a lot!!!

It's works!!!

"Ken Sheridan" пишет:

The following function returns the time difference in the format h:nn:ss

Public Function TimeDuration(dtmFrom As Date, dtmTo As Date) As String

Const HOURSINDAY = 24
Dim strDays As String
Dim strMinutesSeconds As String
Dim dblDuration As Double

dblDuration = dtmTo - dtmFrom

'get number of days
strDays = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

TimeDuration = strDays & strMinutesSeconds

End Function

Call it like so:

=TimeDuration([Date&Time Start], [Date&Time Off])

Ken Sheridan
Stafford, England

How to calculate a difference between Dates in hours & minutes?
There are [Date&Time Start], [Date&Time Off] and [WorkTime]. How to
calculate a difference between [Date&Time Start] and [Date&Time Off] in
hours & minutes in [WorkTime]?