If Statements of were a start time sits within "X" x 3 possibilities

[SmiyLex]

I have a table of start1, end1, start2, and end2 as date/time short time.



What I need to do is create if statements of whether a start1 (or Strat2) fall with in 24:00 <> 6:00:00 or 6:00:00 <> 19:00:00 and or 19:00:00 <> 24:00:00 and on which day either the starts or ends fall.

From the if result I need to call that records linked $rate for $ calc for that shift. Some staff will cover at 3 max combinations of those time/day slots out of 22+ possibilities.

[ranman256]

you only need 2 date fields: Start, End
date and time should be in 1 single field as 2/1/2018 9:00 am.
this way you can do time math.

you dont need 1 field for date, 1 field for time.

[SmiyLex]

you only need 2 date fields: Start, End
date and time should be in 1 single field as 2/1/2018 9:00 am.
this way you can do time math.

you dont need 1 field for date, 1 field for time.

So you mean that to do any maths to any time the field has to be in full format and cannot be in short format.

If: iif( [ShiftHours]![StartDateTime] <6:00:00,"T", "f") Fails at 6: (00) :00 minute point.

The solution is based on the format of the comparison.
The time format is require to be either a percentage decimal of 1 (0.25 = 6:00:00am and 1 =24:00:00pm) or ((6/24) = 6:00:00am and (19/24) = 7:00:00 pm etc...)

[John_G]

If: iif( [ShiftHours]![StartDateTime] <6:00:00,"T", "f") Fails at 6: (00) :00 minute point.

You can extract the hour part of a date/time value with the Hour() function:

iif( hour([ShiftHours]![StartDateTime]) < 6,"T", "f")

The hour() function returns the hour in 24-hour time, i.e. 6 PM is returned as 18.

Be sure you include both Date and time in the form or table field.

[SmiyLex]

You can extract the hour part of a date/time value with the Hour() function:

iif( hour([ShiftHours]![StartDateTime]) < 6,"T", "f")

The hour() function returns the hour in 24-hour time, i.e. 6 PM is returned as 18.

Be sure you include both Date and time in the form or table field.

ranman pointed me to part of the solution Fnx even using short time format. works well for any S/E times within the same day,

now to calculate hrs total worked overnight??

((24-[Start1])+([End1]-0)) gives me the correct hours but in 22/01/1900 8:00:00 AM format not an integer. Were the start is subtracted from midnight and then the midnight till next mornings end is added.

Solution found

DateDiff("h",[Start1],[End1]+1)


Final code to take care of 24hr+ shifting shifts.

HrsWrked: IIf((DateDiff("n",[Start1],[End1],1,1)/60<0),(DateDiff("h",[Start1],[End1]+1) Mod 24),DateDiff("n",[Start1],[End1],1,1)/60)

I was premature in a smily.

The mod 24 portion (DateDiff("h",[Start1],[End1]+1) Mod 24) only returns an integer for example

19:00 (start1) and 4:15 (end1) produces 9 hrs not 9.25

[isladogs]

One way of doing this is using the fact that date/time values are stored as numbers (double)
For this to work you must store both date and time in the same field (or concatenate them)

So to get the hours worked, use

Round(24 * (CDbl([End1]) - CDbl([Start1])), 2)

Using your example 04/03/2018 19:00 (start1) and 05/03/2018 04:15 (end1) produces 9.25 where of course .25 indicates 15 minutes
Dates given here in UK format dd/mm/yyyy

If you want hours and minutes, then use

Fix(24 * (CDbl([End1]) - CDbl([Start1]))) & ":" & (60 * 24 * (CDbl([End1]) - CDbl([Start1])) Mod 60)

This gives 9:15

NOTE: You need to build in error handling to manage any situations where one of the times has not been entered

[John_G]

You have to remember that the format of a date/time is for display purposes only - it has nothing to do with how date/time values are stored internally in Access, or how you use date/time in mathematical operations.

You are almost there with your calculation, though - use datediff() to get the difference in minutes, then divide the result by 60 to get decimal hours:

HoursWorked = datediff("n",[StartTime], [EndTime]) / 60.

But in order for that to work the date as well as the time must be included in [StartTime] and [EndTime]

[SmiyLex]

What I needed is

BasePay:
IIf((DateDiff("n",[Start1],[End1],1,1)/60<0),
((DateDiff("n",[Start1],[End1]+1)/60)*[Employee]![BaseRate]),
(DateDiff("n",[Start1],[End1],1,1)/60)*[Employee]![BaseRate])

It gives me
regular day time gap x $pay Rate or
overnight Time gap x $pay rate

remove the Pay rate and you get time worked as a decimal

[isladogs]

Did you consider the approach I suggested?
That will work for any dates/times whether on the same day or overnight and handle missing times without error

[John_G]

IIf((DateDiff("n",[Start1],[End1],1,1)/60<0)

Why are you testing for a negative here? Since [end1] always follows [Start1], it will never be negative, unless you are not including the date part in [Start1] and [end1] ( which you should be doing). What data are you entering the values into those fields?

[SmiyLex]

Round(24 * (CDbl([End1]) - CDbl([Start1])), 2)

returned negatives but not just negatives but Quantities that I cold not make sense of. the issue is the system is for ordinary people that just want to put in short tme versus the full format.

Cheers anyway.

[isladogs]

Round(24 * (CDbl([End1]) - CDbl([Start1])), 2)

returned negatives but not just negatives but Quantities that I cold not make sense of. the issue is the system is for ordinary people that just want to put in short tme versus the full format.

Cheers anyway.

The approach I suggested uses fields containing both date and time.
If no date is entered, Access treats the date as date zero which was 30 Dec 1899 as far as Access is concerned.

It's possible to add code to solve this.
For example either require a date to be entered or to set any missing date to the current date

[John_G]

...people that just want to put in short tme versus the full format.

Not a problem. Assuming that this is being used in a clock-in - clock-out system, where people are entering the times as soon as they start or end, then you can use a text box to capture the time formatted as hh:mm (I'll call it txtStart as an example). Then, after the time is entered (in the after update event), add the date to it like this:

varTime = date + cdate(me![txtStart])

Cdate() converts the value in txtStart to a format (numeric) that can be added to Date().

I have used a variable here (varTime) to assign the value to, but it could be another textbox on the form. What you could do is have the user enter the time into an unbound text box, then assign the value with the date added to a bound textbox (e.g. bound to StartTime or EndTime), but one which the user doesn't see or which they cannot edit.