Tom’s routine originally was used for a time range that was within the same day, so his table only shows the date with a start and end time. This example would total 6.25 hours
[cid:[email protected]] Now he’s using it for events that span midnight, so if the event starts at 11:00 p.m. and ends the following day at 6:00 a.m., his data validation thinks that the start time comes after the end time, which is “not valid” in most scenarios. If he test for invalid date range and it comes back as “not valid”, then he could assume 11:00 p.m. until midnight + midnight until 6:00 a.m., for a total of 7 hours. Of course, that would allow some “not valid” entries to go undetected (i.e. – if 11:00 p.m. – 6:00 a.m. was entered incorrectly as 11:00 p.m. – 6:00 p.m., instead of showing “not valid” for the input times, it would calculate 1 + 18 hours for a total of 19 hours). This gets even uglier if he assumes times totaling more than 12 hours are wrong, so subtract 12 hours from the total time to bring us back to the 7 hours of time that it should be, but that’s really a lot to assume. I don’t know how he can correct this without adding an end time AND END DATE. Mark Gillespie Marketing Manager / IT Director Elm City Center 1314 W. Walnut St. Jacksonville, IL 62650 (217) 245-9504 ext. 16 - As far as I know, no Elm City Center computer has ever had an undetected error. - From: [email protected] [mailto:[email protected]] On Behalf Of Albert Sent: Friday, March 04, 2016 2:23 PM To: Mark Gillespie Subject: [RBASE-L] - Re: Midnight shift notes Even if he isn't he can calculate the two and do the math. If you have StartDate and StartTime and EndDate and EndTime, just calculate the two DATETIME values and subtract. Albert On 2016-03-04 1:11 PM, [email protected]<mailto:[email protected]> wrote: yes, but Tom doesn't mention whether he's keeping his data in DateTime columns or not... Karen -----Original Message----- From: Javier Valencia <[email protected]><mailto:[email protected]> To: karentellef <[email protected]><mailto:[email protected]> Sent: Fri, Mar 4, 2016 1:54 pm Subject: [RBASE-L] - Re: Midnight shift notes I agree. I use to have date and time as separate columns but now I have switched to DATETIME and avoid all the problems associated with elapsed times that span more than one day. Javier, Javier Valencia, PE O: 913-829-0888 H: 913-397-9605 C: 913-915-3137 From: [email protected]<mailto:[email protected]> [mailto:[email protected]<mailto:[email protected]?>] On Behalf Of Bruce Chitiea Sent: Friday, March 04, 2016 1:46 PM To: [email protected]<mailto:[email protected]> Subject: [RBASE-L] - Re: Midnight shift notes I like Albert's better. Bruce ------ Original Message ------ Sent: 3/4/2016 11:40:56 AM Subject: [RBASE-L] - Re: Midnight shift notes From: "Albert" <[email protected]<mailto:[email protected]>> To: [email protected]<mailto:[email protected]> Cc: For your purposes, you could calculate the full DATETIME value of the shift start and end, and then a simple (EndDateTime-StartDateTime) = Elapsed will give you the elapsed time. Said time will probably be in seconds, so minor adjustments would be necessary. Albert On 2016-03-04 12:11 PM, [email protected]<mailto:[email protected]> wrote: I've never had to do this. Would it help if you had 2 computed date columns that had a DEXTRACT of the start and end? I'm assuming you have 2 DateTime columns now? Because then if the 2 DEXTRACT columns are the same then you can simply do math of the time; if not then you add together 2 times, from beginning to midnight and then from midnight to end. Can't think of anything more elegant... Karen -----Original Message----- From: Tom Frederick mailto:[email protected] To: karentellef mailto:[email protected] Sent: Fri, Mar 4, 2016 12:35 pm Subject: [RBASE-L] - Midnight shift notes We have to track activity by shift and write a note for record purposes. All notes have a staff ID, topic, actual note, date and start/end times which provides proof of activity. Right now I can bill activity by a whole day, but a change to billing by the hour is coming. For day and evening shifts it is pretty easy. They stay same day and within a consecutive time frame. Overnight shifts have to deal with Midnight date/time changes to automatically split over two dates and make 24 hour clock time adjustments. I can check for overlap or bad start/stop times within the same date. Making adjustments for Midnight date/time is just not clicking in my head. Any suggestions are welcome. Tom Frederick President/CEO Elm City Center 1314 W Walnut Jacksonville, IL 62650 O-217-245-9504 F-217-245-2350 [email protected]<mailto:[email protected]><mailto:[email protected]>
