DateDiff Calculation -hours & minutes

[Ruth]

Hi there

I have a query with a formula to calculate the number of hours a load has
taken. It is:

=DateDif("h",[Startloaddatetime],[FinishLoaddatetime])

it shows the number of hours rounded up to the nearest hour. I want it to
show the minutes as well. Is there a way to do this, if so, how is it done?

 

[Graham R Seach]

Ruth,

Try one of these:
http://www.pacificdb.com.au/MVP/Code/Age2.htm
http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Ruth]

Hi Graham

I didn't think it would be so complex. I think that the codes in:

http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

will do the trick. But I am confused as to where to but them and how much
of them to put in. Do I go paste in the the expression builder?
Where do I start and finish with the copy and paste? And where it says
'Date 1' and 'Date 2' - do I replace them with the field names that I have.
If I want it to just show hours and minutes, do I take out the lines refering
to years, months, days and seconds and it will still work?

Please let me know as I think I am a bit over my head here.
--
Thank-you!
Ruth

Ruth,

Try one of these:
http://www.pacificdb.com.au/MVP/Code/Age2.htm
http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Hi there

I have a query with a formula to calculate the number of hours a load has
taken. It is:

=DateDif("h",[Startloaddatetime],[FinishLoaddatetime])

it shows the number of hours rounded up to the nearest hour. I want it to
show the minutes as well. Is there a way to do this, if so, how is it
done?

 

[Douglas J. Steele]

Create a new module (not a class module, nor a module associated with a form
or report) and copy everything between

Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As Date,
_
Optional ShowZero As Boolean = False) As Variant


and

End Function

(including those lines!) into that module. Save the module, making sure NOT
to name the module Diff2Dates when you save it (modules cannot have the same
name as functions or subs)

Once you've done that, replace your current

=DateDiff("h",[Startloaddatetime],[FinishLoaddatetime])

with

=Diff2Dates("hm",[Startloaddatetime],[FinishLoaddatetime])


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Graham

I didn't think it would be so complex. I think that the codes in:

http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

will do the trick. But I am confused as to where to but them and how much
of them to put in. Do I go paste in the the expression builder?
Where do I start and finish with the copy and paste? And where it says
'Date 1' and 'Date 2' - do I replace them with the field names that I
have.
If I want it to just show hours and minutes, do I take out the lines
refering
to years, months, days and seconds and it will still work?

Please let me know as I think I am a bit over my head here.
--
Thank-you!
Ruth

Ruth,

Try one of these:
http://www.pacificdb.com.au/MVP/Code/Age2.htm
http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Hi there

I have a query with a formula to calculate the number of hours a load
has
taken. It is:

=DateDif("h",[Startloaddatetime],[FinishLoaddatetime])

it shows the number of hours rounded up to the nearest hour. I want it
to
show the minutes as well. Is there a way to do this, if so, how is it
done?

 

[Ruth]

Thank-you!

I did this and it partially worked. The minutes are not showing up for some
reason. Just "25 hours" for some reason. Also in the ones with no dates
and times in the fields entered I get an "#Error"

I saved the module as "Module1" there are 2 drop down boxes at the top of
the Module page-- the one on the left has "General" in it and the one on the
right has "Diff2Dates" in it. I changed the one on the right to
"(Declarations)" but it did not make a difference.

What do I do to fix this?

--
Thank-you!
Ruth

Create a new module (not a class module, nor a module associated with a form
or report) and copy everything between

Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As Date,
_
Optional ShowZero As Boolean = False) As Variant


and

End Function

(including those lines!) into that module. Save the module, making sure NOT
to name the module Diff2Dates when you save it (modules cannot have the same
name as functions or subs)

Once you've done that, replace your current

=DateDiff("h",[Startloaddatetime],[FinishLoaddatetime])

with

=Diff2Dates("hm",[Startloaddatetime],[FinishLoaddatetime])


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Graham

I didn't think it would be so complex. I think that the codes in:

http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

will do the trick. But I am confused as to where to but them and how much
of them to put in. Do I go paste in the the expression builder?
Where do I start and finish with the copy and paste? And where it says
'Date 1' and 'Date 2' - do I replace them with the field names that I
have.
If I want it to just show hours and minutes, do I take out the lines
refering
to years, months, days and seconds and it will still work?

Please let me know as I think I am a bit over my head here.
--
Thank-you!
Ruth

Ruth,

Try one of these:
http://www.pacificdb.com.au/MVP/Code/Age2.htm
http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Hi there

I have a query with a formula to calculate the number of hours a load
has
taken. It is:

=DateDif("h",[Startloaddatetime],[FinishLoaddatetime])

it shows the number of hours rounded up to the nearest hour. I want it
to
show the minutes as well. Is there a way to do this, if so, how is it
done?

 

[Douglas J. Steele]

I had a typo. It should be

=Diff2Dates("hn",[Startloaddatetime],[FinishLoaddatetime])

(letter en, not letter em)

Sorry about that...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank-you!

I did this and it partially worked. The minutes are not showing up for
some
reason. Just "25 hours" for some reason. Also in the ones with no dates
and times in the fields entered I get an "#Error"

I saved the module as "Module1" there are 2 drop down boxes at the top of
the Module page-- the one on the left has "General" in it and the one on
the
right has "Diff2Dates" in it. I changed the one on the right to
"(Declarations)" but it did not make a difference.

What do I do to fix this?

--
Thank-you!
Ruth

Create a new module (not a class module, nor a module associated with a
form
or report) and copy everything between

Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As
Date,
_
Optional ShowZero As Boolean = False) As Variant


and

End Function

(including those lines!) into that module. Save the module, making sure
NOT
to name the module Diff2Dates when you save it (modules cannot have the
same
name as functions or subs)

Once you've done that, replace your current

=DateDiff("h",[Startloaddatetime],[FinishLoaddatetime])

with

=Diff2Dates("hm",[Startloaddatetime],[FinishLoaddatetime])


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Graham

I didn't think it would be so complex. I think that the codes in:

http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

will do the trick. But I am confused as to where to but them and how
much
of them to put in. Do I go paste in the the expression builder?
Where do I start and finish with the copy and paste? And where it says
'Date 1' and 'Date 2' - do I replace them with the field names that I
have.
If I want it to just show hours and minutes, do I take out the lines
refering
to years, months, days and seconds and it will still work?

Please let me know as I think I am a bit over my head here.
--
Thank-you!
Ruth


:

Ruth,

Try one of these:
http://www.pacificdb.com.au/MVP/Code/Age2.htm
http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Hi there

I have a query with a formula to calculate the number of hours a
load
has
taken. It is:

=DateDif("h",[Startloaddatetime],[FinishLoaddatetime])

it shows the number of hours rounded up to the nearest hour. I want
it
to
show the minutes as well. Is there a way to do this, if so, how is
it
done?

 

[Ruth]

Thank-you very much! It works now.

Is there a way to have a blank rather than "#Error" in the ones when the
fields are not filled out?
--
Thank-you!
Ruth

I had a typo. It should be

=Diff2Dates("hn",[Startloaddatetime],[FinishLoaddatetime])

(letter en, not letter em)

Sorry about that...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank-you!

I did this and it partially worked. The minutes are not showing up for
some
reason. Just "25 hours" for some reason. Also in the ones with no dates
and times in the fields entered I get an "#Error"

I saved the module as "Module1" there are 2 drop down boxes at the top of
the Module page-- the one on the left has "General" in it and the one on
the
right has "Diff2Dates" in it. I changed the one on the right to
"(Declarations)" but it did not make a difference.

What do I do to fix this?

--
Thank-you!
Ruth

Create a new module (not a class module, nor a module associated with a
form
or report) and copy everything between

Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As
Date,
_
Optional ShowZero As Boolean = False) As Variant


and

End Function

(including those lines!) into that module. Save the module, making sure
NOT
to name the module Diff2Dates when you save it (modules cannot have the
same
name as functions or subs)

Once you've done that, replace your current

=DateDiff("h",[Startloaddatetime],[FinishLoaddatetime])

with

=Diff2Dates("hm",[Startloaddatetime],[FinishLoaddatetime])


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Graham

I didn't think it would be so complex. I think that the codes in:

http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

will do the trick. But I am confused as to where to but them and how
much
of them to put in. Do I go paste in the the expression builder?
Where do I start and finish with the copy and paste? And where it says
'Date 1' and 'Date 2' - do I replace them with the field names that I
have.
If I want it to just show hours and minutes, do I take out the lines
refering
to years, months, days and seconds and it will still work?

Please let me know as I think I am a bit over my head here.
--
Thank-you!
Ruth


:

Ruth,

Try one of these:
http://www.pacificdb.com.au/MVP/Code/Age2.htm
http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Hi there

I have a query with a formula to calculate the number of hours a
load
has
taken. It is:

=DateDif("h",[Startloaddatetime],[FinishLoaddatetime])

it shows the number of hours rounded up to the nearest hour. I want
it
to
show the minutes as well. Is there a way to do this, if so, how is
it
done?

 

[Ruth]

Hi Douglas

Another question as arised now. I have another field in the same query that
uses the hours and minutes from the formula below to find out the load rate
and now I get an error.
It formulas is:

DockLoadRateData![Tonnage]/[DockHrMin])

How do I modify this formula so it works?


--
Thank-you!
Ruth

I had a typo. It should be

=Diff2Dates("hn",[Startloaddatetime],[FinishLoaddatetime])

(letter en, not letter em)

Sorry about that...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank-you!

I did this and it partially worked. The minutes are not showing up for
some
reason. Just "25 hours" for some reason. Also in the ones with no dates
and times in the fields entered I get an "#Error"

I saved the module as "Module1" there are 2 drop down boxes at the top of
the Module page-- the one on the left has "General" in it and the one on
the
right has "Diff2Dates" in it. I changed the one on the right to
"(Declarations)" but it did not make a difference.

What do I do to fix this?

--
Thank-you!
Ruth

Create a new module (not a class module, nor a module associated with a
form
or report) and copy everything between

Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As
Date,
_
Optional ShowZero As Boolean = False) As Variant


and

End Function

(including those lines!) into that module. Save the module, making sure
NOT
to name the module Diff2Dates when you save it (modules cannot have the
same
name as functions or subs)

Once you've done that, replace your current

=DateDiff("h",[Startloaddatetime],[FinishLoaddatetime])

with

=Diff2Dates("hm",[Startloaddatetime],[FinishLoaddatetime])


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Graham

I didn't think it would be so complex. I think that the codes in:

http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

will do the trick. But I am confused as to where to but them and how
much
of them to put in. Do I go paste in the the expression builder?
Where do I start and finish with the copy and paste? And where it says
'Date 1' and 'Date 2' - do I replace them with the field names that I
have.
If I want it to just show hours and minutes, do I take out the lines
refering
to years, months, days and seconds and it will still work?

Please let me know as I think I am a bit over my head here.
--
Thank-you!
Ruth


:

Ruth,

Try one of these:
http://www.pacificdb.com.au/MVP/Code/Age2.htm
http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Hi there

I have a query with a formula to calculate the number of hours a
load
has
taken. It is:

=DateDif("h",[Startloaddatetime],[FinishLoaddatetime])

it shows the number of hours rounded up to the nearest hour. I want
it
to
show the minutes as well. Is there a way to do this, if so, how is
it
done?

 

[Douglas J. Steele]

Displaying the difference is hours and minutes converts it to a string, so
you cannot use it in calculations.

You'll need to determine the difference in minutes (using
=DateDif("n",[Startloaddatetime],[FinishLoaddatetime])), and divide that by
60.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas

Another question as arised now. I have another field in the same query
that
uses the hours and minutes from the formula below to find out the load
rate
and now I get an error.
It formulas is:

DockLoadRateData![Tonnage]/[DockHrMin])

How do I modify this formula so it works?


--
Thank-you!
Ruth

I had a typo. It should be

=Diff2Dates("hn",[Startloaddatetime],[FinishLoaddatetime])

(letter en, not letter em)

Sorry about that...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank-you!

I did this and it partially worked. The minutes are not showing up for
some
reason. Just "25 hours" for some reason. Also in the ones with no
dates
and times in the fields entered I get an "#Error"

I saved the module as "Module1" there are 2 drop down boxes at the top
of
the Module page-- the one on the left has "General" in it and the one
on
the
right has "Diff2Dates" in it. I changed the one on the right to
"(Declarations)" but it did not make a difference.

What do I do to fix this?

--
Thank-you!
Ruth


:

Create a new module (not a class module, nor a module associated with
a
form
or report) and copy everything between

Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As
Date,
_
Optional ShowZero As Boolean = False) As Variant


and

End Function

(including those lines!) into that module. Save the module, making
sure
NOT
to name the module Diff2Dates when you save it (modules cannot have
the
same
name as functions or subs)

Once you've done that, replace your current

=DateDiff("h",[Startloaddatetime],[FinishLoaddatetime])

with

=Diff2Dates("hm",[Startloaddatetime],[FinishLoaddatetime])


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Graham

I didn't think it would be so complex. I think that the codes in:

http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

will do the trick. But I am confused as to where to but them and
how
much
of them to put in. Do I go paste in the the expression builder?
Where do I start and finish with the copy and paste? And where it
says
'Date 1' and 'Date 2' - do I replace them with the field names that
I
have.
If I want it to just show hours and minutes, do I take out the lines
refering
to years, months, days and seconds and it will still work?

Please let me know as I think I am a bit over my head here.
--
Thank-you!
Ruth


:

Ruth,

Try one of these:
http://www.pacificdb.com.au/MVP/Code/Age2.htm
http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Hi there

I have a query with a formula to calculate the number of hours a
load
has
taken. It is:

=DateDif("h",[Startloaddatetime],[FinishLoaddatetime])

it shows the number of hours rounded up to the nearest hour. I
want
it
to
show the minutes as well. Is there a way to do this, if so, how
is
it
done?

 

[Douglas J. Steele]

Change the declaration from

Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As Date,

to

Public Function Diff2Dates(Interval As String, Date1 As Variant, Date2 As
Variant,

then add the following code after the declarations, but before any other
code:

If IsNull(Date1) Or IsNull(Date2) Then
Exit Function
End If

,

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank-you very much! It works now.

Is there a way to have a blank rather than "#Error" in the ones when the
fields are not filled out?
--
Thank-you!
Ruth

I had a typo. It should be

=Diff2Dates("hn",[Startloaddatetime],[FinishLoaddatetime])

(letter en, not letter em)

Sorry about that...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank-you!

I did this and it partially worked. The minutes are not showing up for
some
reason. Just "25 hours" for some reason. Also in the ones with no
dates
and times in the fields entered I get an "#Error"

I saved the module as "Module1" there are 2 drop down boxes at the top
of
the Module page-- the one on the left has "General" in it and the one
on
the
right has "Diff2Dates" in it. I changed the one on the right to
"(Declarations)" but it did not make a difference.

What do I do to fix this?

--
Thank-you!
Ruth


:

Create a new module (not a class module, nor a module associated with
a
form
or report) and copy everything between

Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As
Date,
_
Optional ShowZero As Boolean = False) As Variant


and

End Function

(including those lines!) into that module. Save the module, making
sure
NOT
to name the module Diff2Dates when you save it (modules cannot have
the
same
name as functions or subs)

Once you've done that, replace your current

=DateDiff("h",[Startloaddatetime],[FinishLoaddatetime])

with

=Diff2Dates("hm",[Startloaddatetime],[FinishLoaddatetime])


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Graham

I didn't think it would be so complex. I think that the codes in:

http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

will do the trick. But I am confused as to where to but them and
how
much
of them to put in. Do I go paste in the the expression builder?
Where do I start and finish with the copy and paste? And where it
says
'Date 1' and 'Date 2' - do I replace them with the field names that
I
have.
If I want it to just show hours and minutes, do I take out the lines
refering
to years, months, days and seconds and it will still work?

Please let me know as I think I am a bit over my head here.
--
Thank-you!
Ruth


:

Ruth,

Try one of these:
http://www.pacificdb.com.au/MVP/Code/Age2.htm
http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Hi there

I have a query with a formula to calculate the number of hours a
load
has
taken. It is:

=DateDif("h",[Startloaddatetime],[FinishLoaddatetime])

it shows the number of hours rounded up to the nearest hour. I
want
it
to
show the minutes as well. Is there a way to do this, if so, how
is
it
done?

 

[Ruth]

Thank-you! It works now
--
Thank-you!
Ruth

Displaying the difference is hours and minutes converts it to a string, so
you cannot use it in calculations.

You'll need to determine the difference in minutes (using
=DateDif("n",[Startloaddatetime],[FinishLoaddatetime])), and divide that by
60.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas

Another question as arised now. I have another field in the same query
that
uses the hours and minutes from the formula below to find out the load
rate
and now I get an error.
It formulas is:

DockLoadRateData![Tonnage]/[DockHrMin])

How do I modify this formula so it works?


--
Thank-you!
Ruth

I had a typo. It should be

=Diff2Dates("hn",[Startloaddatetime],[FinishLoaddatetime])

(letter en, not letter em)

Sorry about that...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thank-you!

I did this and it partially worked. The minutes are not showing up for
some
reason. Just "25 hours" for some reason. Also in the ones with no
dates
and times in the fields entered I get an "#Error"

I saved the module as "Module1" there are 2 drop down boxes at the top
of
the Module page-- the one on the left has "General" in it and the one
on
the
right has "Diff2Dates" in it. I changed the one on the right to
"(Declarations)" but it did not make a difference.

What do I do to fix this?

--
Thank-you!
Ruth


:

Create a new module (not a class module, nor a module associated with
a
form
or report) and copy everything between

Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As
Date,
_
Optional ShowZero As Boolean = False) As Variant


and

End Function

(including those lines!) into that module. Save the module, making
sure
NOT
to name the module Diff2Dates when you save it (modules cannot have
the
same
name as functions or subs)

Once you've done that, replace your current

=DateDiff("h",[Startloaddatetime],[FinishLoaddatetime])

with

=Diff2Dates("hm",[Startloaddatetime],[FinishLoaddatetime])


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Graham

I didn't think it would be so complex. I think that the codes in:

http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

will do the trick. But I am confused as to where to but them and
how
much
of them to put in. Do I go paste in the the expression builder?
Where do I start and finish with the copy and paste? And where it
says
'Date 1' and 'Date 2' - do I replace them with the field names that
I
have.
If I want it to just show hours and minutes, do I take out the lines
refering
to years, months, days and seconds and it will still work?

Please let me know as I think I am a bit over my head here.
--
Thank-you!
Ruth


:

Ruth,

Try one of these:
http://www.pacificdb.com.au/MVP/Code/Age2.htm
http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Hi there

I have a query with a formula to calculate the number of hours a
load
has
taken. It is:

=DateDif("h",[Startloaddatetime],[FinishLoaddatetime])

it shows the number of hours rounded up to the nearest hour. I
want
it
to
show the minutes as well. Is there a way to do this, if so, how
is
it
done?