# Comparing Times in Access

#### ARW17

##### Board Regular
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.

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### xenou

##### MrExcel MVP
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
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
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

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
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

</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
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
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?

Replies
9
Views
1K
Replies
3
Views
940
Replies
65
Views
2K
Replies
11
Views
566
Replies
3
Views
543

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,528
Messages
5,832,277
Members
430,125
Latest member
iubrownie

### 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.

### Which adblocker are you using?

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

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