Midnight Time calculation Help....

[Big_Rob]

New guy here...Using v2007.

I have a db used in manufacturing and in a machining report I capture [Start Time] and [End Time] (=[End Time]-[Start Time]). I only record Time (short) and not date/time. I can't used DateDiff.

The problem occurs on the 3rd shift when end time is past midnight on to the next day....Help

 

[spikepl]

So you could record date/time in full. Nothing prevents you from adding date, internally.

Updated:

Or do some shaky logic: if EndTime<StartTime then duration would be 24 - (StartTime-EndTime)

 

[Big_Rob]

So you could record date/time in full. Nothing prevents you from adding date, internally.

Or do some shaky logic: if StartTime<EndTime then duration would be 24 - (StartTime-EndTime)

I've googled the heck out of this subject and can't believe that there's nothing out there for this....

 

[spikepl]

But there is: logic. If you throw away information by not storing date, then you have to compensate for it by using what you know - if endtime<starttime it means it's across midnight (as long as a midnight shift starts after noon, and ends before noon the next day.

 

[Brianwarnock]

The problem is getting that 24 hours in since you can't enter #24:00#, you have to take 2 bites at this with say 23 + 1 , I wonder why people don't just collect the date?

brian

 

[Big_Rob]

I'm an Access rookie....
There has to be someone out there that can modify this for midnight...

=[End Time]-[Start Time]

 

[Big_Rob]

Still looking....but nothing out there?

 

[Galaxiom]

=IIF(Endtime-StartTime<0, 1+Endtime-StartTime, EndTime-StartTime)

This will probably give you the answer as a time of day in Hours, Minutes and Seconds.

 

[MarkK]

OMG, record the date. What data system doesn't record the date of an event?

 

[Galaxiom]

OMG, record the date. What data system doesn't record the date of an event?

Spike suggested including the date in the first post. (Along with a red herring but that is another story)

Apparently Rob either didn't understand, or the system he is connecting to only supplies the time. I am just guessing because he never actually engaged with us or explained why the first suggestion wasn't suitable.

Most of the helpers here don't respond well to those who post questions, ignore what is suggested, and especially when they come back simply bumping the topic, still with no feedback.

The fact is there is nothing out there because it isn't done that way. Even if only the time was supplied by an external system a developer would typcally combine it with the date during the import and certainly before attempting to do the arithmetic.

 

[spikepl]

Spike suggested including the date in the first post. (Along with a red herring but that is another story)

Ooops shame on me. You are quite right.

 

[Big_Rob]

Re: Midnight Time calculation Help....SOLVED!!!

OMG, record the date. What data system doesn't record the date of an event?

I stay away from the BS that usually fills most forums...

Apparently Rob either didn't understand, or the system he is connecting to only supplies the time. I am just guessing because he never actually engaged with us or explained why the first suggestion wasn't suitable.

Most of the helpers here don't respond well to those who post questions, ignore what is suggested, and especially when they come back simply bumping the topic, still with no feedback.

The fact is there is nothing out there because it isn't done that way. Even if only the time was supplied by an external system a developer would typcally combine it with the date during the import and certainly before attempting to do the arithmetic.

Galaxiom, thanks for actually attempting find a solution...although, I didn't try yours. I had posted the same question in various places.

Code:

=IIF(Endtime-StartTime<0, 1+Endtime-StartTime, EndTime-StartTime)

Here is the solution that was sent to me and it works perfectly!!!

From RP...
Date/time fields actually hold a number, where the integer portion
represents the date as days since 30/12/1899, and the decimal portion
represents the time as portion of 24 hours (eg. .5 is 12 hours). If you're
only storing the time component, the date component is 0. What you need to
do, when End Time is less than Start Time, is to add a day to the End Time.
So you can set the controlsource for your elapsed time textbox to the
following expression:

=IIf([End Time]>[Start Time],[End Time]-[Start Time],([End Time]+1)-[Start Time])

 

[MarkK]

BS? Yeah, you're right. This is better...

=IIf([End Time]>[Start Time],[End Time]-[Start Time],([End Time]+1)-[Start Time])

Super clever. Why record the actual data when you can fudge it with an un-readable work-around?
And this, are you serious???

I had posted the same question in various places.

Thanks for posting back and letting us know how much of our time you wasted.

 

[Galaxiom]

lagbolt:

Don't worry about it mate. Rob obviously knows a lot more than we do and he won't bother coming here in future but instead go to the other forum where the smart people post.

Some can't be told about good practices. Maybe he wants to save storage space by only recording time.

Sometimes it is easier to just give them the kludge they want so they go away. That is why I posted the solution.

 

[Brianwarnock]

At least he did come back, if only to prove that he did not understand what had already been posted here, many just disappear no comment or feedback, a situation very prevalent in the excel sub forum.

Brian

 

[ambrn14]

I also am having a problem with this. I am just trying to find how many times an event happened during this date and time range. My data is divided date and time fields. When I enter "18:45" as my "txtStartTime" and "06:45" as my "txtEndTime", my result gives me times from 06:45-18:45 instead of from 18:45-06:45.
My query is as follows:

SELECT Count(tblCodeStrokePts.CodeStrokeID) AS SpecificActivationTimes
FROM tblCodeStrokePts
WHERE (((tblCodeStrokePts.CodeStrokeActivationTime) Between [Forms]![frmQuerySwitchboard]![txtStartTime] And [Forms]![frmQuerySwitchboard]![txtEndTime]) AND ((tblCodeStrokePts.CodeStrokeDate) Between [Forms]![frmQuerySwitchboard]![txtStartdate] And [Forms]![frmQuerySwitchboard]![txtEnddate]) AND ((tblCodeStrokePts.Canceled) Is Null) AND ((tblCodeStrokePts.Missed) Is Null));

How can I capture data during this "evening/night" time period? HELP!

ambrn

 

[Brianwarnock]

YOu need proper Date/Time fields not split fields.

Between...And... is sneaker/clever depending on your point of view and always starts with the earlier Date/Time.

Brian

 

[ambrn14]

Is there any way around having Date/Time fields? My whole db have split date/time fields.

And I am not sure that I follow your comment on "Between. . And. . .".

ambrn

 

[Brianwarnock]

Between dteField1 And dteField2 will look for date/time values between the earliest and latest values in the 2 fields whichever way round they are, in your case 06:45 is before 18:45 as the Date in each case is 0 or 31/12/1899.

Create a true Date/time value on the fly Datefield + Timefield

Brian

 

[ambrn14]

Thanks for the suggestion. I will let you know if I get it working later. Have to go for now.

ambrn