Calculating time over midnight

[Guest]

Hi all,

I have 2 fields in a table - TimeOff and TimeOn. I have been able to work
out the difference between the 2 using DateDiff, but, when the time on is set
as 00:00:00 for midnight I get the wrong answer (eg if TimeOff is 23:00:00
and TimeOn is 00:00:00 I get 23 not 1)

Secondly I can't group by the main field (AppNo) and get a total of hours.

Can anyone help on this?

Regards

Andy

 

[Van T. Dinh]

See The Access Web article:

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

 

[Guest]

Thank you, that did the trick, however I am not able to sum my times now,
which I can do if I use Datediff.

Have you any other suggestions?

Thanks

Andy

 

[Van T. Dinh]

You can use the technique in the article for the *display* of the single
intervals AND your DateDiff() for your summation.

 

[Guest]

I'm sorry, I can't see what you mean by this. The Datediff doesn't take into
account going over midnight.

Thanks

Andy

 

[Van T. Dinh]

You wrote previously:

"Thank you, that did the trick, however I am not able to sum my times now,
which I can do if I use Datediff"

.... so what did you mean by your statement, especially the last paragraph???

 

[Guest]

Ah, my apologies.

OK, so using the function on the page you referenced I can calculate the
number of hours per item. However when I then try to group and sum the
totals, it tells me there is a data mismatch error. Can I do something that
will now total these times?

Regards

Andy

 

[Van T. Dinh]

It sounds to me that you are using DateTime Field but only use / store the
time component values. The date component is defaulted to 0, i.e.
31/Dec/1899 in this case. In addition, this also creates difficulties going
over midnight (as you have seen).

I mentioned that the return of the technique mentioned in the article is
actually String and only suitable for display, not for further calculations
such as adding durations.

If you want to use this result for calculations, you need to use the CDate()
function to convert it to a Date/Time value befor summation. Note that if
the total of the sum goes over 24 hours, the total will probably be
displayed incorrectly (since the max of the time component is 23:59:59).

 

[Guest]

I'm really sorry, I thought it was making sense, but I'm just not getting it.
It's so frustrating.

I have tried:

HoursOut: [TimeIn]-1-[TimeOut] This gives me the correct hours but I can't
group

HoursOut: DateDiff("n",[TimeIn],[TimeOut]) gives me the incorrect number of
hours for midnight crossing but allows me to group.

I can't see how to use the CDate()

Thanks for your help Van,

Andy

 

[John Vinson]

I'm really sorry, I thought it was making sense, but I'm just not getting it.
It's so frustrating.

I have tried:

HoursOut: [TimeIn]-1-[TimeOut] This gives me the correct hours but I can't
group

HoursOut: DateDiff("n",[TimeIn],[TimeOut]) gives me the incorrect number of
hours for midnight crossing but allows me to group.

The problem is that *your data structure is wrong*.

11:00pm *is later than* 5:00am. It's 18 hours later. The difference of
time between 11:00pm and 5:00am is -18 hours. That's just a *fact*,
not a bug in the software!

If you will be dealing with times spanning midnight, you will be mired
in ambiguity unless you store the date and time together in the same
field. DateDiff("n", #10/18/2006 11:00pm#, #10/19/2006 05:00am#)
returns 360 (six hours), just as you are wanting; it makes no
difference whether you span midnight or not.

If you are really stuck with the ambiguous time values, all on
December 30, 1899 (which is how Access handles date/time values), then
you'll need a more complex expression. You'll need to ASSUME that
you'll never have a TimeOut more than 24 hours after TimeIn, so that
you can check to see if TimeOut is earlier than TimeIn and add 24
hours if so:

HoursOut: (DateDiff("n", [TimeIn], [TimeOut]) + IIF([TimeIn] >
[TimeOut], 1440, 0)) / 60.

This will give you the -18 hours = 1080 minutes in the example above,
and add 1440 to get back to the "other side of the clock".

John W. Vinson[MVP]

 

[Van T. Dinh]

You cannot use:

HoursOut: [TimeIn]-1-[TimeOut]

since this will give you the wrong duration. The article I referred to uses
this WITH the Format() function to convert to Text to display what looks
like the correct time duration but the datetime Value is actually wrong (it
has the date interval of -1 day).

You can use the CDate() like (using the example from The Access Web
article):

?CDate(format(#23:45#-1-#00:15#,"short time"))
00:30:00

Personally, I prefer to use the inbuilt VBA functions to derive durations
from time points, e.g. DateDiff. If your time periods are *never* more than
24 hours you can use something like:

StartTime = #23:45#
EndTime = #00:15#
?DateDiff("n", StartTime, IIf(EndTime > StartTime, EndTime, DateAdd("d", 1,
EndTime)))
30

i.e. giving you the *correct* 30 mins. You can the sum the intervals up to
get the total minutes (and then convert to hours + minutes if required).

John Vinson's method will work fine also.

However, as John wrote, if you need to work out time duration from time
points, it is always better to store bothe date + time in the time points.

--
HTH
Van T. Dinh
MVP (Access)

I'm really sorry, I thought it was making sense, but I'm just not getting
it.
It's so frustrating.

I have tried:

HoursOut: [TimeIn]-1-[TimeOut] This gives me the correct hours but I
can't
group

HoursOut: DateDiff("n",[TimeIn],[TimeOut]) gives me the incorrect number
of
hours for midnight crossing but allows me to group.

I can't see how to use the CDate()

Thanks for your help Van,

Andy

 

[Guest]

Hi John,

Thank you, this does work, I get my hours calculated and I am able to group.

However (you just knew there would be one eh?) some of the shifts finish
dead on midnight. When this occurs the function is adding an unwanted 24
hours. How can I get round that?

Regards

Andy

I'm really sorry, I thought it was making sense, but I'm just not getting it.
It's so frustrating.

I have tried:

HoursOut: [TimeIn]-1-[TimeOut] This gives me the correct hours but I can't
group

HoursOut: DateDiff("n",[TimeIn],[TimeOut]) gives me the incorrect number of
hours for midnight crossing but allows me to group.

The problem is that *your data structure is wrong*.

11:00pm *is later than* 5:00am. It's 18 hours later. The difference of
time between 11:00pm and 5:00am is -18 hours. That's just a *fact*,
not a bug in the software!

If you will be dealing with times spanning midnight, you will be mired
in ambiguity unless you store the date and time together in the same
field. DateDiff("n", #10/18/2006 11:00pm#, #10/19/2006 05:00am#)
returns 360 (six hours), just as you are wanting; it makes no
difference whether you span midnight or not.

If you are really stuck with the ambiguous time values, all on
December 30, 1899 (which is how Access handles date/time values), then
you'll need a more complex expression. You'll need to ASSUME that
you'll never have a TimeOut more than 24 hours after TimeIn, so that
you can check to see if TimeOut is earlier than TimeIn and add 24
hours if so:

HoursOut: (DateDiff("n", [TimeIn], [TimeOut]) + IIF([TimeIn] >
[TimeOut], 1440, 0)) / 60.

This will give you the -18 hours = 1080 minutes in the example above,
and add 1440 to get back to the "other side of the clock".

John W. Vinson[MVP]

 

[Guest]

Hi Van

Thank you, this also works, I get my hours calculated and I am able to group.

However (just as I added to John) some of the shifts finish dead on
midnight. When this occurs the function is adding an unwanted 24 hours. How
can I get round that?

Regards

Andy

You cannot use:

HoursOut: [TimeIn]-1-[TimeOut]

since this will give you the wrong duration. The article I referred to uses
this WITH the Format() function to convert to Text to display what looks
like the correct time duration but the datetime Value is actually wrong (it
has the date interval of -1 day).

You can use the CDate() like (using the example from The Access Web
article):

?CDate(format(#23:45#-1-#00:15#,"short time"))
00:30:00

Personally, I prefer to use the inbuilt VBA functions to derive durations
from time points, e.g. DateDiff. If your time periods are *never* more than
24 hours you can use something like:

StartTime = #23:45#
EndTime = #00:15#
?DateDiff("n", StartTime, IIf(EndTime > StartTime, EndTime, DateAdd("d", 1,
EndTime)))
30

i.e. giving you the *correct* 30 mins. You can the sum the intervals up to
get the total minutes (and then convert to hours + minutes if required).

John Vinson's method will work fine also.

However, as John wrote, if you need to work out time duration from time
points, it is always better to store bothe date + time in the time points.

--
HTH
Van T. Dinh
MVP (Access)

I'm really sorry, I thought it was making sense, but I'm just not getting
it.
It's so frustrating.

I have tried:

HoursOut: [TimeIn]-1-[TimeOut] This gives me the correct hours but I
can't
group

HoursOut: DateDiff("n",[TimeIn],[TimeOut]) gives me the incorrect number
of
hours for midnight crossing but allows me to group.

I can't see how to use the CDate()

Thanks for your help Van,

Andy

It sounds to me that you are using DateTime Field but only use / store
the
time component values. The date component is defaulted to 0, i.e.
31/Dec/1899 in this case. In addition, this also creates difficulties
going
over midnight (as you have seen).

I mentioned that the return of the technique mentioned in the article is
actually String and only suitable for display, not for further
calculations
such as adding durations.

If you want to use this result for calculations, you need to use the
CDate()
function to convert it to a Date/Time value befor summation. Note that
if
the total of the sum goes over 24 hours, the total will probably be
displayed incorrectly (since the max of the time component is 23:59:59).

 

[Van T. Dinh]

How do the user enter midnight? 00:00 or 24:00 or 12:00am?

I don't think 24:00 is possible with a datetime field and both 00:00 and
12:00AM are correct since this is midnight of *next* day (in the
calculations) so you need to add 1 day in the calculations. Thus adding 1
day to dead midnight end is correct.