By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,501 Members | 831 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,501 IT Pros & Developers. It's quick & easy.

Add up minutes 23:30 -04:05 next day??

P: n/a
ACC 2000

I am having problems working out how to adding up minutes?
Example:using short time

Date Timestart Timend Minutes

25-jan-04 15:00 16:00 60
25-Jan-04 19:05 21:10 125
25-Jan-04 23:00 04:00 Can't make it work!!

The problem is when it trips over into a new day..
I just can't make the minutes add up..
Appreciate any help....
Thanks
Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
You should look into the datediff function in help. It will ease your mind.
There are others as well like dateadd and datepart. You will have to
consolidate your date and time into one field though.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access
"Alex" <ge****@yas1.freeserve.co.uk> wrote in message
news:b7**************************@posting.google.c om...
ACC 2000

I am having problems working out how to adding up minutes?
Example:using short time

Date Timestart Timend Minutes

25-jan-04 15:00 16:00 60
25-Jan-04 19:05 21:10 125
25-Jan-04 23:00 04:00 Can't make it work!!

The problem is when it trips over into a new day..
I just can't make the minutes add up..
Appreciate any help....
Thanks

Nov 12 '05 #2

P: n/a
DateDiff will give you the difference between two date times in minutes or
other units. Your problem, of course, is that you only have one date to go
with the time... so you need to determine if the second time represents a
new day. That can be easy or difficult, depending on the range of times.

If the difference is never more than a few hours, it'll be relatively easy.
If, however, you can start at 3PM one day and stop at 8PM the next, it will
be not only difficult, but impossible unless you supply the second date.

Calculate the minutes in a query, use Running Total on the field in the
Report.

Larry Linson
Microsoft Access MVP

"Alex" <ge****@yas1.freeserve.co.uk> wrote in message
news:b7**************************@posting.google.c om...
ACC 2000

I am having problems working out how to adding up minutes?
Example:using short time

Date Timestart Timend Minutes

25-jan-04 15:00 16:00 60
25-Jan-04 19:05 21:10 125
25-Jan-04 23:00 04:00 Can't make it work!!

The problem is when it trips over into a new day..
I just can't make the minutes add up..
Appreciate any help....
Thanks

Nov 12 '05 #3

P: n/a
try

TodayDate = "1/19/04"
TodayTIME = "08:00"

ToMORROWDate = "1/20/04"
ToMORROWTIME = "13:00"

MyDate01 = todaydate & " " & todaytime & " AM"

MyDate02 = toMORROWdate & " " & toMORROWtime & " PM"

datediff(. . . .. .

John Bickmore
www.BicycleCam.com
www.Feed-Zone.com
"Larry Linson" <bo*****@localhost.not> wrote in message
news:k7*****************@nwrddc03.gnilink.net...
DateDiff will give you the difference between two date times in minutes or
other units. Your problem, of course, is that you only have one date to go
with the time... so you need to determine if the second time represents a
new day. That can be easy or difficult, depending on the range of times.

If the difference is never more than a few hours, it'll be relatively easy. If, however, you can start at 3PM one day and stop at 8PM the next, it will be not only difficult, but impossible unless you supply the second date.

Calculate the minutes in a query, use Running Total on the field in the
Report.

Larry Linson
Microsoft Access MVP

"Alex" <ge****@yas1.freeserve.co.uk> wrote in message
news:b7**************************@posting.google.c om...
ACC 2000

I am having problems working out how to adding up minutes?
Example:using short time

Date Timestart Timend Minutes

25-jan-04 15:00 16:00 60
25-Jan-04 19:05 21:10 125
25-Jan-04 23:00 04:00 Can't make it work!!

The problem is when it trips over into a new day..
I just can't make the minutes add up..
Appreciate any help....
Thanks


Nov 12 '05 #4

P: n/a
Sure, works fine if you know that the 13:00 time is tomorrow; but with only
one date, it could as well be today.

Larry Linson
Microsoft Access MVP

"xzzy" <mr********@comcast.net> wrote in message
news:FoYOb.85789$sv6.203765@attbi_s52...
try

TodayDate = "1/19/04"
TodayTIME = "08:00"

ToMORROWDate = "1/20/04"
ToMORROWTIME = "13:00"

MyDate01 = todaydate & " " & todaytime & " AM"

MyDate02 = toMORROWdate & " " & toMORROWtime & " PM"

datediff(. . . .. .

John Bickmore
www.BicycleCam.com
www.Feed-Zone.com
"Larry Linson" <bo*****@localhost.not> wrote in message
news:k7*****************@nwrddc03.gnilink.net...
DateDiff will give you the difference between two date times in minutes or other units. Your problem, of course, is that you only have one date to go with the time... so you need to determine if the second time represents a new day. That can be easy or difficult, depending on the range of times.

If the difference is never more than a few hours, it'll be relatively

easy.
If, however, you can start at 3PM one day and stop at 8PM the next, it

will
be not only difficult, but impossible unless you supply the second date.

Calculate the minutes in a query, use Running Total on the field in the
Report.

Larry Linson
Microsoft Access MVP

"Alex" <ge****@yas1.freeserve.co.uk> wrote in message
news:b7**************************@posting.google.c om...
ACC 2000

I am having problems working out how to adding up minutes?
Example:using short time

Date Timestart Timend Minutes

25-jan-04 15:00 16:00 60
25-Jan-04 19:05 21:10 125
25-Jan-04 23:00 04:00 Can't make it work!!

The problem is when it trips over into a new day..
I just can't make the minutes add up..
Appreciate any help....
Thanks



Nov 12 '05 #5

P: n/a
ge****@yas1.freeserve.co.uk (Alex) wrote in message news:<b7**************************@posting.google. com>...
ACC 2000

I am having problems working out how to adding up minutes?
Example:using short time

Date Timestart Timend Minutes

25-jan-04 15:00 16:00 60
25-Jan-04 19:05 21:10 125
25-Jan-04 23:00 04:00 Can't make it work!!

The problem is when it trips over into a new day..
I just can't make the minutes add up..

You need to use DateDiff("n",[TimeStart],[TimeEnd]). Also, you should
store the date AND time in the same field as a general date mm-dd-yyyy
hh:nn:ss or whatever format you want. Also, see this article.

So your You would have something like this:

TimeStart TimeEnd Minutes
25-jan-04 15:00 25-jan-04 16:00 60
25-jan-04 23:00 26-jan-04 04:00 300

(Don't store the date as a separate field - make it part of the both
the TimeStart and the TimeEnd date/time fields, and format each as
general date, and you shouldn't have any more problems.

HTH,
Pieter

http://www.mvps.org/access/datetime/date0008.htm

There are others there... look up "Midnight" on the search page...
Nov 12 '05 #6

P: n/a
Larry Linson wrote:
Sure, works fine if you know that the 13:00 time is tomorrow; but with only
one date, it could as well be today.
One suggestion, in this day and age of gigabyte hard drives, is to not act
stingy on diskspace and to add a datefield for the end time and enter the date.
That would end it. An even simpler method is to use NOW() values for start and
end instead of Date() in one field and Time() in another.He could make that mod
to the table easy enough and with an update query change the fields to NOW()
values. With the FORMAT() function, he can easily display dates and times.

Of course, the person could do a check and verify if the TO Time is less than or
greater than the FROM time. If less than, then add a day to the start date.
Since this calc is probably doing with employee work hours or delivery times,
the range in hours is most likely less than 24. Simply more overhead.

In this case, the developer that initiated the post needs to plan better on the
data he stores.....IMHO
Larry Linson
Microsoft Access MVP

"xzzy" <mr********@comcast.net> wrote in message
news:FoYOb.85789$sv6.203765@attbi_s52...
try

TodayDate = "1/19/04"
TodayTIME = "08:00"

ToMORROWDate = "1/20/04"
ToMORROWTIME = "13:00"

MyDate01 = todaydate & " " & todaytime & " AM"

MyDate02 = toMORROWdate & " " & toMORROWtime & " PM"

datediff(. . . .. .

John Bickmore
www.BicycleCam.com
www.Feed-Zone.com
"Larry Linson" <bo*****@localhost.not> wrote in message
news:k7*****************@nwrddc03.gnilink.net...
DateDiff will give you the difference between two date times in minutes or other units. Your problem, of course, is that you only have one date to go with the time... so you need to determine if the second time represents a new day. That can be easy or difficult, depending on the range of times.

If the difference is never more than a few hours, it'll be relatively

easy.
If, however, you can start at 3PM one day and stop at 8PM the next, it

will
be not only difficult, but impossible unless you supply the second date.

Calculate the minutes in a query, use Running Total on the field in the
Report.

Larry Linson
Microsoft Access MVP

"Alex" <ge****@yas1.freeserve.co.uk> wrote in message
news:b7**************************@posting.google.c om...
> ACC 2000
>
> I am having problems working out how to adding up minutes?
>
>
> Example:using short time
>
> Date Timestart Timend Minutes
>
> 25-jan-04 15:00 16:00 60
> 25-Jan-04 19:05 21:10 125
> 25-Jan-04 23:00 04:00 Can't make it work!!
>
> The problem is when it trips over into a new day..
> I just can't make the minutes add up..
>
>
> Appreciate any help....
>
>
> Thanks



Nov 12 '05 #7

P: n/a
You are correct: that would, no doubt about it, be a more efficient way to
store and handle the data. It may not be a more efficient way to have
someone enter the data, though. Also, the data may come from some other
software, or be captured from inputs to some other software. Even so, it
might be worthwhile to convert it to true date/time values after it is input
or captured. On the other hand, if it comes from other software or is
captured from that input stream, getting the date that corresponds to the
second time may not be an option.

Larry Linson
Microsoft Access MVP

"Salad" <oi*@vinegar.com> wrote in message
news:40***************@vinegar.com...
Larry Linson wrote:
Sure, works fine if you know that the 13:00 time is tomorrow; but with only one date, it could as well be today.
One suggestion, in this day and age of gigabyte hard drives, is to not act
stingy on diskspace and to add a datefield for the end time and enter the

date. That would end it. An even simpler method is to use NOW() values for start and end instead of Date() in one field and Time() in another.He could make that mod to the table easy enough and with an update query change the fields to NOW() values. With the FORMAT() function, he can easily display dates and times.
Of course, the person could do a check and verify if the TO Time is less than or greater than the FROM time. If less than, then add a day to the start date. Since this calc is probably doing with employee work hours or delivery times, the range in hours is most likely less than 24. Simply more overhead.

In this case, the developer that initiated the post needs to plan better on the data he stores.....IMHO
Larry Linson
Microsoft Access MVP

"xzzy" <mr********@comcast.net> wrote in message
news:FoYOb.85789$sv6.203765@attbi_s52...
try

TodayDate = "1/19/04"
TodayTIME = "08:00"

ToMORROWDate = "1/20/04"
ToMORROWTIME = "13:00"

MyDate01 = todaydate & " " & todaytime & " AM"

MyDate02 = toMORROWdate & " " & toMORROWtime & " PM"

datediff(. . . .. .

John Bickmore
www.BicycleCam.com
www.Feed-Zone.com
"Larry Linson" <bo*****@localhost.not> wrote in message
news:k7*****************@nwrddc03.gnilink.net...
> DateDiff will give you the difference between two date times in minutes
or
> other units. Your problem, of course, is that you only have one date
to go
> with the time... so you need to determine if the second time
represents a
> new day. That can be easy or difficult, depending on the range of

times. >
> If the difference is never more than a few hours, it'll be relatively easy.
> If, however, you can start at 3PM one day and stop at 8PM the next, it will
> be not only difficult, but impossible unless you supply the second date. >
> Calculate the minutes in a query, use Running Total on the field in the > Report.
>
> Larry Linson
> Microsoft Access MVP
>
> "Alex" <ge****@yas1.freeserve.co.uk> wrote in message
> news:b7**************************@posting.google.c om...
> > ACC 2000
> >
> > I am having problems working out how to adding up minutes?
> >
> >
> > Example:using short time
> >
> > Date Timestart Timend Minutes
> >
> > 25-jan-04 15:00 16:00 60
> > 25-Jan-04 19:05 21:10 125
> > 25-Jan-04 23:00 04:00 Can't make it work!!
> >
> > The problem is when it trips over into a new day..
> > I just can't make the minutes add up..
> >
> >
> > Appreciate any help....
> >
> >
> > Thanks
>
>

Nov 12 '05 #8

P: n/a
CDB
Days too late, but what about

DateDiff("n",(Cstr(EventDate) & " " & Cstr(TimeStart)),(Cstr(EventDate) & "
" & Cstr(TimeEnd)))

Clive
"Alex" <ge****@yas1.freeserve.co.uk> wrote in message
news:b7**************************@posting.google.c om...
ACC 2000

I am having problems working out how to adding up minutes?
Example:using short time

Date Timestart Timend Minutes

25-jan-04 15:00 16:00 60
25-Jan-04 19:05 21:10 125
25-Jan-04 23:00 04:00 Can't make it work!!

The problem is when it trips over into a new day..
I just can't make the minutes add up..


Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.