Comparing Times in Access

ARW17

Board Regular
Joined
Oct 31, 2016
Messages
97
I'm working on an employee schedule. I have folks start and end times as well as break and lunch times.

I'm trying to create 1/0 for working/not working in 15-minute intervals throughout the day.

My equation looks to say if the interval is less that the person's start time, greater than the person's end time or equal to a break or lunch time, then 0 (they aren't working during that interval), else 1 (they are working during that interval).

On many intervals, the following equation works.

Working: IIf(([Time Intervals]![IntervalStart]<[tblSchedules]![StartTime]) Or ([Time Intervals]![IntervalStart]>[tblSchedules]![End]) Or ([Time Intervals]![IntervalStart]=[tblSchedules]![AMBreak]) Or ([Time Intervals]![IntervalStart]=[tblSchedules]![Lunch1]) Or ([Time Intervals]![IntervalStart]=[tblSchedules]![Lunch2]) Or ([Time Intervals]![IntervalStart]=[tblSchedules]![Lunch3]) Or ([Time Intervals]![IntervalStart]=[tblSchedules]![PMBreak]),0,1)

However, [Time Intervals]![Interval Start] might be 7:45 AM and [tblSchedules]![StartTime] might be 7:45:00 AM and Access doesn't realize these should match. The format on both tables is Date/Time, Medium Time.

I've copied into Excel and back and I still get these issues.

I've tried using a rounding function and thought it may have worked, but again it seems to be a mix and match of these types of discrepancies so rounding isn't always the answer (in fact, sometimes it took a correct answer and turned it into the wrong answer).

I can't be the only one trying to compare times in Access, but I haven't found a post that addresses this directly.

Any help is appreciated.

Thanks in advance!
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
HOw is your data structured? Is it a table with three fields (EmployeeID, time starting, and time ending)? Or something else?

Also format should mean nothing and shouldn't matter if your data is REAL dates. MSAccess will look at the underlying data value regardless of format. Unless you have (or are comparing) Dates Stored As Text - which changes everything.
 

ARW17

Board Regular
Joined
Oct 31, 2016
Messages
97
My data is Employee ID, Start Time, AM Break, Lunch 1, Lunch 2, Lunch 3, PM Break, End.

The formats are all date/time.

I think I solved my issue by using: round([Time Intervals]![IntervalStart],2) on each time.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
To me that isn't going to work for a time interval, since rounding doesn't guarantee you get a value back in whole minutes. But still not sure what your data looks like - are those fields your data you are looking at, or data you are trying to get out of it? Doesn't quite look like a real time card since it has nothing for the start/end times (except the first and last fields).
 

ARW17

Board Regular
Joined
Oct 31, 2016
Messages
97

ADVERTISEMENT

To me that isn't going to work for a time interval, since rounding doesn't guarantee you get a value back in whole minutes. But still not sure what your data looks like - are those fields your data you are looking at, or data you are trying to get out of it? Doesn't quite look like a real time card since it has nothing for the start/end times (except the first and last fields).

Here's a sample of my data:
EmployeeDepartmentDayWorkFromHomeAMendPMstartStartTimeAMBreakLunch1Lunch2Lunch3PMBreakEnd
Jones, BobPhone Contact CenterMonday1:45 PM2:30 PM9:45 AM11:15 AM1:00 PM1:15 PM1:30 PM4:00 PM6:30 PM
Smith, JohnPhone Contact CenterMonday
12:00 PM12:45 PM8:00 AM9:45 AM12:15 PM12:30 PM12:45 PM3:30 PM4:45 PM
Coy, BillEntryMonday
11:45 AM12:30 PM7:45 AM9:45 AM11:45 AM12:00 PM12:15 PM2:15 PM4:30 PM
Falvo, EmmersonSales OperationsMonday
1:45 PM2:30 PM9:45 AM2:45 PM12:15 PM12:30 PM12:45 PM4:30 PM6:30 PM
Lilly, JillPhone Contact CenterMonday
1:45 PM2:30 PM9:45 AM11:30 AM1:00 PM1:15 PM1:30 PM4:15 PM6:30 PM
Cougar, BJPhone Contact CenterMonday
11:15 AM12:00 PM7:15 AM9:45 AM11:45 AM12:00 PM12:15 PM2:30 PM4:00 PM

<tbody>
</tbody>
 
Last edited:

ARW17

Board Regular
Joined
Oct 31, 2016
Messages
97
Here's what I got out for Jones, Bob:
DepartmentWorkFromHomeEmployeeDayRankIntervalStartWorkingFromHome
Phone Contact Center Jones, BobMonday15:45 AM0
Phone Contact Center Jones, BobMonday16:00 AM0
Phone Contact Center Jones, BobMonday16:15 AM0
Phone Contact Center Jones, BobMonday16:30 AM0
Phone Contact Center Jones, BobMonday16:45 AM0
Phone Contact Center Jones, BobMonday17:00 AM0
Phone Contact Center Jones, BobMonday17:15 AM0
Phone Contact Center Jones, BobMonday17:30 AM0
Phone Contact Center Jones, BobMonday17:45 AM0
Phone Contact Center Jones, BobMonday18:00 AM0
Phone Contact Center Jones, BobMonday18:15 AM0
Phone Contact Center Jones, BobMonday18:30 AM0
Phone Contact Center Jones, BobMonday18:45 AM0
Phone Contact Center Jones, BobMonday19:00 AM0
Phone Contact Center Jones, BobMonday19:15 AM0
Phone Contact Center Jones, BobMonday19:30 AM0
Phone Contact Center Jones, BobMonday19:45 AM1
Phone Contact Center Jones, BobMonday110:00 AM1
Phone Contact Center Jones, BobMonday110:15 AM1
Phone Contact Center Jones, BobMonday110:30 AM1
Phone Contact Center Jones, BobMonday110:45 AM1
Phone Contact Center Jones, BobMonday111:00 AM1
Phone Contact Center Jones, BobMonday111:15 AM0
Phone Contact Center Jones, BobMonday111:30 AM1
Phone Contact Center Jones, BobMonday111:45 AM1
Phone Contact Center Jones, BobMonday112:00 PM1
Phone Contact Center Jones, BobMonday112:15 PM1
Phone Contact Center Jones, BobMonday112:30 PM1
Phone Contact Center Jones, BobMonday112:45 PM1
Phone Contact Center Jones, BobMonday11:00 PM0
Phone Contact Center Jones, BobMonday11:15 PM0
Phone Contact Center Jones, BobMonday11:30 PM0
Phone Contact Center Jones, BobMonday11:45 PM1
Phone Contact Center Jones, BobMonday12:00 PM1
Phone Contact Center Jones, BobMonday12:15 PM1
Phone Contact Center Jones, BobMonday12:30 PM1
Phone Contact Center Jones, BobMonday12:45 PM1
Phone Contact Center Jones, BobMonday13:00 PM1
Phone Contact Center Jones, BobMonday13:15 PM1
Phone Contact Center Jones, BobMonday13:30 PM1
Phone Contact Center Jones, BobMonday13:45 PM1
Phone Contact Center Jones, BobMonday14:00 PM0
Phone Contact Center Jones, BobMonday14:15 PM1
Phone Contact Center Jones, BobMonday14:30 PM1
Phone Contact Center Jones, BobMonday14:45 PM1
Phone Contact Center Jones, BobMonday15:00 PM1
Phone Contact Center Jones, BobMonday15:15 PM1
Phone Contact Center Jones, BobMonday15:30 PM1
Phone Contact Center Jones, BobMonday15:45 PM1
Phone Contact Center Jones, BobMonday16:00 PM1
Phone Contact Center Jones, BobMonday16:15 PM1
Phone Contact Center Jones, BobMonday16:30 PM0
Phone Contact Center Jones, BobMonday16:45 PM0
Phone Contact Center Jones, BobMonday17:00 PM0

<caption> Schedule </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>
****** id="cke_pastebin" style="position: absolute; top: 85px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
Jones, Bob

<tbody>
</tbody>
</body>
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
What does Lunch1, Lunch2, and Lunch3 mean for Jones, Bob? Seems like they are start times without end times, so not sure what the interpretation means (it is confusing to mark an "interval" with only one value, since by definition an interval has a start and an end - two values).

However, in general, you do what you just did - which is be careful, test results, and make sure things work. Dates are stored in MSAccess as decimal numbers. Things like .4564232113 and such. In general if you enter a date as 1:00PM (For instance) it will get a value that is equal to another date entered as 1:00PM (for instance). Personally, I don't even trust a simple statement such as that. but it all comes down to what you are trying to do - add things, or find overlap, or find beginning points or ending points.
 
Last edited:

Russell Hauf

MrExcel MVP
Joined
Feb 10, 2002
Messages
1,611
May I also (in addition to xenou's questions) ask why you want to get the 15-min intervals? What are you then going to do with those?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,206
Messages
5,594,833
Members
413,943
Latest member
Dhornsby21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top