time difference calculation help

[JayRab]

Ok have a [Time Job Started] field that is auto populated with now(). which has input of 1/29/2014 11:02:02 am. Then I have a [Time Job Ended], typed in by user that has input of 6:00 pm, no date value. Cant really do the date() + Time Job Ended, because the date can range since times can go through midnight.



I am trying to calculate time duration. DateDiff("n",[Hot Calls]![Time Job started],[hot Calls]![Time Job Ended]) if this formatted as "Short time" it returns 0:00.

I want to report this as HH:MM. so the result should be 6:58.

Thanks for any help.

[ssanfu]

Cant really do the date() + Time Job Ended, because the date can range since times can go through midnight

You must have the end date and time to calculate the duration. Or else assume the end date is the same as the start date. If someone is entering the time, they could pick a date from a date picker control or type in the date.

Somehow you must have the user enter the end date... and the time. Then calculate the duration whenever you need it..

[rpeare]

assuming your times don't cross over the midnight hour

SELECT HotCalls.TimeJobStarted, HotCalls.TimeJobEnded, CDate(Format([timejobstarted],"Short Date") & " " & [timejobended]) AS ConvertedEndTime, DateDiff("n",[timejobstarted],(CDate(Format([timejobstarted],"Short Date") & " " & [timejobended]))) AS NumberofMinutes, Int(DateDiff("n",[timejobstarted],(CDate(Format([timejobstarted],"Short Date") & " " & [timejobended])))/60) AS HourCalc, DateDiff("n",[timejobstarted],(CDate(Format([timejobstarted],"Short Date") & " " & [timejobended]))) Mod 60 AS MinCalc, Int(DateDiff("n",[timejobstarted],(CDate(Format([timejobstarted],"Short Date") & " " & [timejobended])))/60) & ":" & Right("0" & ((DateDiff("n",[timejobstarted],(CDate(Format([timejobstarted],"Short Date") & " " & [timejobended]))) Mod 60)),2) AS FinalTime
FROM HotCalls;

I left a bunch of calculation fields in there so you can see how I put it together.

[JayRab]

Yeah unforunately the times do cross over midnight. I have currently added buttons to capture most of the times and dates...but if they manually enter time and not select date first if throws off calculations. then I just manipulate the data.

Ok on existing data, I do have a shift date and shift type as a TempVar. So if they are on night shift(7pm-7am) and timejob ended is after midnight.

 If TempVars!shift = "Night" then
  If [Time Job Ended]> #00:00# And [Time Job Ended] <#07:00# then 
    strdate = TempVars!Shift +1
    Strtime = strdate + [Time Job ended]
 end if
else
'Dayshift stuff
end if

The just use an update query to set these dates.. or just add a field that calculates above?

Which is the best way you guys think?

Thanks for help

[rpeare]

Well the next question would be, do you have jobs that cross multiple days or even more than 12 hours, for instance if you have a job that starts at 11:59:59 PM and the end of their shift is no later than, say, 8:00 AM (when the next shift takes over) you can safely assume in your calculation if the date is missing you can use the start date/time's date value, otherwise if the end date value is there use that instead. You shouldn't need to futz with an arbitrary tempvars (for the sake of argument I'm saying it's arbitrary because your shifts could change and you'd want an easier way to handle going from, say, 8 hour shifts to 12 hours shifts)

If you wanted to be sure the date value was populated before you did any sort of queries/reporting on your data you could just run an update query on your data but it's really not necessary (though it would be cleaner for someone not part of your development to pick up the pieces if you vanish tomorrow)

[JayRab]

Jobs can only last for a specific shift. A new Job ID is created for each shift each day. So a piece of equipment can be down for weeks, under 1 equiment downtime ID number and attached to the equipment downtime ID number are multiple Job ID numbers.

Anyway I got the reports to work properly and from there I will use the query to append this information to a table.
In order to save this information for later analysis I will need to get automated as well.

Time Job Ended_: IIf([Hot Calls]![Time Job Ended]<[Hot Calls]![Shift Date],IIf([Hot Calls]![Shift]="Day Shift",[Hot Calls]![Shift Date]+[Hot Calls]![Time Job Ended],IIf([Hot Calls]![Time Job Ended]>#12:00:00 AM# And [Hot Calls]![Time Job Ended]<#12:00:00 PM#,[Hot Calls]![Shift Date]+1+[Hot Calls]![Time Job Ended],[Hot Calls]![Shift Date]+[Hot Calls]![Time Job Ended])),[Hot Calls]![Time Job Ended])

Just need to make sure above code works for all time entries...need to get my links set back to my testing back end and spend some time trying to mess it up lol.

thanks again for all the help.