Date/Time Average??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that works well, BUT, I have one problem. How do I get an
average of the date and times on the report footer

I'm using diff2dates on the report which works well:
=diff2dates("hn",CDate([bed Dte] & " " & [bed time]),CDate([rec Dte] & " " &
[rec Time]))

Displays:
23 minutes
1 hour 23 minutes
etc.....

But When I put this code on the report footer, it doesn't work. I want to
average this data on the report footer

=Avg(diff2dates("hn",CDate([bed Dte] & " " & [bed time]),CDate([rec Dte] & "
" & [rec Time])))


This doesn't work?? Can some one help, I need to
 
Try:
=diff2dates("hn",CDate([rec Dte] & " " & [rec Time]) - CDate([bed Dte] & " "
& [bed time]))

I might have something reversed but I trust you can figure that out.
 
Since diff2dates is returning a string, you can't use average on it.

Try averaging the difference in minutes (using DateDiff), and format that
into hh:mm.
 
This doesn't work at all. It gives me and error message "wrong number of
arguments"
I'm trying to get an average of all the data in the report footer using the
diff2dates funtion.

Duane Hookom said:
Try:
=diff2dates("hn",CDate([rec Dte] & " " & [rec Time]) - CDate([bed Dte] & " "
& [bed time]))

I might have something reversed but I trust you can figure that out.
--
Duane Hookom
MS Access MVP
--

bladelock said:
I have a report that works well, BUT, I have one problem. How do I get an
average of the date and times on the report footer

I'm using diff2dates on the report which works well:
=diff2dates("hn",CDate([bed Dte] & " " & [bed time]),CDate([rec Dte] & " "
&
[rec Time]))

Displays:
23 minutes
1 hour 23 minutes
etc.....

But When I put this code on the report footer, it doesn't work. I want to
average this data on the report footer

=Avg(diff2dates("hn",CDate([bed Dte] & " " & [bed time]),CDate([rec Dte] &
"
" & [rec Time])))


This doesn't work?? Can some one help, I need to
Click to expand...
Click to expand...
 
I put this in the "Report Footer"
=Avg(diff2dates("n",CDate([bed Dte] & " " & [bed time]),CDate([rec Dte] & "
" & [rec Time])))

Still get an error



Douglas J. Steele said:
Since diff2dates is returning a string, you can't use average on it.

Try averaging the difference in minutes (using DateDiff), and format that
into hh:mm.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



bladelock said:
I have a report that works well, BUT, I have one problem. How do I get an
average of the date and times on the report footer

I'm using diff2dates on the report which works well:
=diff2dates("hn",CDate([bed Dte] & " " & [bed time]),CDate([rec Dte] & " "
&
[rec Time]))

Displays:
23 minutes
1 hour 23 minutes
etc.....

But When I put this code on the report footer, it doesn't work. I want to
average this data on the report footer

=Avg(diff2dates("hn",CDate([bed Dte] & " " & [bed time]),CDate([rec Dte] &
"
" & [rec Time])))


This doesn't work?? Can some one help, I need to
Click to expand...
Click to expand...
 
I said you need to use DateDiff (the built-in VBA function), not diff2dates.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



bladelock said:
I put this in the "Report Footer"
=Avg(diff2dates("n",CDate([bed Dte] & " " & [bed time]),CDate([rec Dte] &
"
" & [rec Time])))

Still get an error



Douglas J. Steele said:
Since diff2dates is returning a string, you can't use average on it.

Try averaging the difference in minutes (using DateDiff), and format that
into hh:mm.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



bladelock said:
I have a report that works well, BUT, I have one problem. How do I get
an
average of the date and times on the report footer

I'm using diff2dates on the report which works well:
=diff2dates("hn",CDate([bed Dte] & " " & [bed time]),CDate([rec Dte] &
" "
&
[rec Time]))

Displays:
23 minutes
1 hour 23 minutes
etc.....

But When I put this code on the report footer, it doesn't work. I want
to
average this data on the report footer

=Avg(diff2dates("hn",CDate([bed Dte] & " " & [bed time]),CDate([rec
Dte] &
"
" & [rec Time])))


This doesn't work?? Can some one help, I need to
Click to expand...
Click to expand...
Click to expand...
 
Sorry, I did use datediff "error" empression is to complex
=Avg(DateDiff("hn",CDate([bed Dte] & " " & [bed time]),CDate([rec Dte] & " "
& [rec Time])))


bladelock said:
I put this in the "Report Footer"
=Avg(diff2dates("n",CDate([bed Dte] & " " & [bed time]),CDate([rec Dte] & "
" & [rec Time])))

Still get an error



Douglas J. Steele said:
Since diff2dates is returning a string, you can't use average on it.

Try averaging the difference in minutes (using DateDiff), and format that
into hh:mm.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



bladelock said:
I have a report that works well, BUT, I have one problem. How do I get an
average of the date and times on the report footer

I'm using diff2dates on the report which works well:
=diff2dates("hn",CDate([bed Dte] & " " & [bed time]),CDate([rec Dte] & " "
&
[rec Time]))

Displays:
23 minutes
1 hour 23 minutes
etc.....

But When I put this code on the report footer, it doesn't work. I want to
average this data on the report footer

=Avg(diff2dates("hn",CDate([bed Dte] & " " & [bed time]),CDate([rec Dte] &
"
" & [rec Time])))


This doesn't work?? Can some one help, I need to
Click to expand...
Click to expand...
Click to expand...
 
There is no "hn" in DateDiff(). Try:
=Avg(CDate([rec Dte] & " " & [rec Time]) - CDate([bed Dte] & " " & [bed
time]))
This assumes rec is earlier than bed.


--
Duane Hookom
MS Access MVP


bladelock said:
Sorry, I did use datediff "error" empression is to complex
=Avg(DateDiff("hn",CDate([bed Dte] & " " & [bed time]),CDate([rec Dte] & "
"
& [rec Time])))


bladelock said:
I put this in the "Report Footer"
=Avg(diff2dates("n",CDate([bed Dte] & " " & [bed time]),CDate([rec Dte] &
"
" & [rec Time])))

Still get an error



Douglas J. Steele said:
Since diff2dates is returning a string, you can't use average on it.

Try averaging the difference in minutes (using DateDiff), and format
that
into hh:mm.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have a report that works well, BUT, I have one problem. How do I get
an
average of the date and times on the report footer

I'm using diff2dates on the report which works well:
=diff2dates("hn",CDate([bed Dte] & " " & [bed time]),CDate([rec Dte]
& " "
&
[rec Time]))

Displays:
23 minutes
1 hour 23 minutes
etc.....

But When I put this code on the report footer, it doesn't work. I
want to
average this data on the report footer

=Avg(diff2dates("hn",CDate([bed Dte] & " " & [bed time]),CDate([rec
Dte] &
"
" & [rec Time])))


This doesn't work?? Can some one help, I need to
Click to expand...
Click to expand...
Click to expand...
 
Back
Top