Difference Between Times Past Midnight

Odeh Naber · November 11th, 2009, 06:50 AM

Hello!

On an Access form I have three controls:

StartTime
EndTime
TimeDuration

They are all 'Short Time' formatted and are bound to fields in the same table. The user inputs manually the StartTime and EndTime using the input mask 00:00;0;_ (all he needs to register is hour and time in 24-hour format. And the TimeDuration should automatically be calculated and populated.

I have successfuly been able to calculate the time difference, but not if the end time goes past midnight.

For example: if the StartTime is 21:00 and the end time is 01:00, then the result should give 04:00, but it gives 20h00. I already understand why it does that (Access assumes they are both times on the same date) - but I can't figure out how to overcome this.

I have tried many options, from a basic formula ([StartTime] - [EndTime]), which gives the result above) and also tried using DateDiff which always gives me 00:00 as the result, regardless. I am certain I am using the function correctly: DateDiff("n", [StartTime],[EndTime]).

Any clues on how I can overcome this using any function or code?

Appreciate any help! Thank you!

gbianchi · November 11th, 2009, 09:29 AM

The problem will be how to know if the two dates are on the same day...

You can assume that if end is lower or equal to start, then if will be on other day...

using that, equals the date of the time and add a day to the end point.. that will solve your problem...

Odeh Naber · November 11th, 2009, 11:16 AM

Cheers for replying!

I tried to setting up an afterupdate code on both the StartTime and EndTime controls on the form. So after the user updates either the StartTime or EndTime, the following code would run and populate the TimeDuration control with the result, but it always gave me a 00:00 result. I always get the 00:00 result whether or not EndTime passes midnight. Not sure why (see below for the code). Anything appear wrong with the code?:

Thanks!

If Me.EndTime.Value <= Me.StartTime.Value Then
Me.TimeDuration.Value = DateDiff("n", Me.StartTime.Value, Me.EndTime.Value) + 1440
Else
Me.TimeDuration.Value = DateDiff("n", Me.StartTime.Value, Me.EndTime.Value)
End If

I also tried creating two new unbound controls in the form - StartDate and EndDate. The StartDate is populated by the user, and the EndDate is populated automatically based on the StartTime and EndTime. So, if EndTime is less than StartTime, EndDate will automatically populate as StartDate + 1. Then I created two unbound General Date controls which automatically combined the StartDate and StartTime into one General Date control, and the EndDate and EndTime into the other General Date control. The attempt here was to then use the DateDiff to calculate the duration between the two - but I also got the 00:00 result here.

Any clues? Cheers!

gbianchi · November 11th, 2009, 01:32 PM

I don't undestand what type of control is TimeDuration. Datediff is returning a long in your case, so what value are you expecting to be shown on TimeDuration??

Anyway, simpler that this is just add a day (or 1440 minutes) to the EndTime.. then try the datediff...

DateAdd("d", 1, EndTime)
something!!! = DateDiff("n", Me.StartTime.Value, Me.EndTime.Value)

Remember that datediff could return you a negative value!!! (if endtime is before start time)....

Odeh Naber · November 12th, 2009, 05:52 AM

Sorry for not explaining - so the StartTime, EndTime and TimeDuration are all exactly the same. Basically, the below is an example of what I am trying to achieve:

StartTime: 21:00 (only minutes and hours - manually entered into the box by the user)
EndTime: 23:00 (only minutes and hours - manually entered into the box by the user)
TimeDuration: 02:00 (only minutes and hours - automatically calculated and stored)

another example:

StartTime: 22:00 (only minutes and hours - manually entered into the box by the user)
EndTime: 01:00 (only minutes and hours - manually entered into the box by the user)
TimeDuration: 03:00 (only minutes and hours - automatically calculated and stored)

I think I have a little breakthrough - apparantely I can only use the DateDiff and DateAdd functions if the StartTime and EndTime fields are both General Time, and if the user inputs the full date and time in each one of these fields. However, this can be a lengthy tasks - I would prefer the user registers only the time. I already have a short date field in the form - is there anyway to combine it with the StartTime so that the new combined field can be used in the DateDiff?

For example:
StartTime: 21h00
EndTime: 22h00
StartDate: 21-05-2009

Combine StartTime and StartDate into one field: 21-05-2009 21h00
Combine EndTime and EndDate into one field: 21-05-2009 22h00
(in the case that EndTime is smaller than StartTime, then the EndTime/EndDate combination would be 22-05-2009 22h00 (EndTime+1). I tried =[StartDate] & " " & [StartTime] but it doesnt work.

This assumes that the StartTime and EndTime are in short time format, and the StartDate is in ShortDate format.

Thanks!

Odeh Naber · November 12th, 2009, 06:23 AM

I figured it out! Sorry - So I managed to combine the date and time fields, and then using DateDiff I was able to calculate the number of minutes between the two dates. I then used MOD to convert the value to hh:mm and the result was perfect!

I can provide more details of the solution if you want :) but thanks for all your help!

Regards,

gbianchi · November 12th, 2009, 08:16 AM

Great for you. Please provide your solution so other people that found the forum could use it.

SerranoG · November 25th, 2009, 10:26 AM

Congratulations, but you should also note that you should not be storing the duration at all. Calculated fields are not stored; they are recalculated at run-time when needed to display on a form or report. You are bloating your database with extra data.

Odeh Naber · November 25th, 2009, 01:44 PM

Thanks! I was thinking of storing the duration in order to avoid having to program the same procedure for a report as well :) Like this at least the report will be easier to recreate. But I do agree with you! :)

I will post the solution in the next days - sorry was out of town :) Cheers!

Odeh Naber · November 26th, 2009, 05:31 AM

So here is what I did - in this case the duration was not stored :) (I am not a pro at this so please let me know if there is something that wasnt clear and I will do my best to explain!) Cheers!:

I created the following in a form:

Date (Short Date - bound to field in table)
StartTime (Short Time - bound to field in table)
EndTime (Short Time - bound to field in table)
Duration (Short Time - unbound)

So the user would input the date, the start time and end time manually only. The rest is done automatically. As I was able to understand, if we use the DateDiff on two ShortTime values, 'going past midnight' will not be considered, which is a problem. However it can be done using two GeneralDate values. So to overcome this problem, I created two separate unbound fields in which the database automatically creates the StartTime and EndTime in General Date format - the following two unbounds are both in General Date format:

StartTimeCalc = [Date] & " " & [Start Time]
EndTimeCalc = IIf([EndTime]<[StartTime];[Date]+1 & " " & [EndTime];[Date] & " " & [EndTime])

Now we have two General Date values that we can use to calculate the duration. The database continues to automatically calculate the duration:

Duration = (DateDiff("n";[StartTimeCalc];[EndTimeCalc]))\60 & Format((DateDiff("n";[StartTimeCalc];[EndTimeCalc])) Mod 60;"\:00")

This last bit took me a very long time to figure out and to understand - I think it was mostly luck I guess using trial and error to create the formula.

Hope this was clear! :) Cheers!