UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Working Hours - Night Shift, Access 2016    
 
   
Quabba
post Aug 2 2019, 04:14 AM
Post#1



Posts: 115
Joined: 30-May 14



Hi guys,

I've created a form which lets users input start and finish times on a daily basis.
It also shows the correct number of hours worked each week.

My problem is we have just started a night shift in the factory and if the user puts in a start time of 22:30 and finish of 06:30 the total hours comes out as a minus.

I know this is because of the date going into a new day or something along those lines.

Can anyone tell me how to make this work for nightshift also?

Thanks
Go to the top of the page
 
dale.fye
post Aug 2 2019, 05:21 AM
Post#2



Posts: 161
Joined: 28-March 18
From: Virginia


you need to store both the date and the time for start and end work.

Are you currently doing this in a date field or are you storing the start and end times as strings?

you could also test for whether the Start time is greater than the end time, and if so, add 1 (a whole day) to the end time when computing hours worked.

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
Quabba
post Aug 2 2019, 05:28 AM
Post#3



Posts: 115
Joined: 30-May 14



I'm currently using this to calculate the daily hours worked, the user types in the start and end times using short time format

=DateDiff("n",[txt_StartHour],[txt_EndHour])

I use this to show the total hours for the week

=[txt_Totalhours]\60 & Format([txt_Totalhours] Mod 60,"\:00")
Go to the top of the page
 
BruceM
post Aug 2 2019, 09:48 AM
Post#4


UtterAccess VIP
Posts: 7,993
Joined: 24-May 10
From: Downeast Maine


Dates in Access are the number of days since December 30, 1899 (day 0), plus the decimal portion of the day. Day 1 is December 31, 1899. 1.5 is noon on that day. I believe the reason why Day 1 isn't January 1, 1900 is that somebody forgot that January 1900 wasn't a leap year.

Short time format in a text field, or are you using a date/time field? If the latter, Access assumes Day 0, but does not display it unless you specify. Similarly, if no time of day is specified, midnight (hour, minute, and second 0) is assumed. If somebody's start time is 8:00 PM (20:00) and the end time 02:00, Access assumes the same day unless you specify otherwise. That is why dale.fye suggested as one of the options that you could test whether the end time is a smaller number than the start time, although best is to include the day.

QUOTE
[txt_Totalhours]\60

The slash should tilt the other way if the intent is to do division.

Go to the top of the page
 
RJD
post Aug 2 2019, 10:43 AM
Post#5


UtterAccess VIP
Posts: 10,157
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI. From what you have said in your last post, we can assume that the start and end times are in a date/time field and that the date is not included. As such, a date IS included, by default, as always in this type field, but defaults to the first day of the calendar (as Bruce explained). I agree with both Dale and Bruce that you will have to calculate the new end date/time if the end time is before the start time. I thought I'd just add a way to do that...

=IIf([txt_StartHour]>[txt_EndHour],DateDiff("n",[txt_StartHour],[txt_EndHour]+1),DateDiff("n",[txt_StartHour],[txt_EndHour]))

Or even ...

=DateDiff("n",[txt_StartHour],[txt_EndHour]-([txt_StartHour]>[txt_EndHour]))

(shorter, but perhaps a bit harder to follow)

This assumes that you are putting this in a textbox on a form or report. But I recommend that you do this in a query instead.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
strive4peace
post Aug 4 2019, 08:32 AM
Post#6


strive4peace
Posts: 20,464
Joined: 10-January 04



as a point of interest, since Bruce brought it up, this is why day 1 is Dec 31, 1899 in Access ...
In Excel, day 1 is January 1, 1900, which is one more than day 1 for Access. This makes recent serial dates match between the 2 products. Does Excel have one more day than Access somewhere? Yes. When creating Excel, Microsoft chose to propagate a bug from Lotus 1-2-3 where Feb 29, 1900 was counted as a day; but 1900 was actually not a leap year. On March 1, 1900, the serial dates in Excel and Access match, and continue to do so after that.

~~
since you are just getting a difference between 2 times, Joe's trick to add 1 to the end time if the start time is greater should work just fine


This post has been edited by strive4peace: Aug 4 2019, 08:35 AM

--------------------
have an awesome day,
crystal
Go to the top of the page
 
Quabba
post Aug 5 2019, 07:40 AM
Post#7



Posts: 115
Joined: 30-May 14



thanks to all of you for your input and explaining things a bit clearer to me.

I've used your first example Joe and works a treat.

Thanks again
Go to the top of the page
 
RJD
post Aug 5 2019, 08:18 AM
Post#8


UtterAccess VIP
Posts: 10,157
Joined: 25-October 10
From: Gulf South USA


You are very welcome, from all of us. Gad you got that working.

Continued success ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th December 2019 - 03:42 PM