Comparing Times in Access

A

ARW17

Board Regular
Joined
Oct 31, 2016
Messages
109
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!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
xenou said:
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).
Click to expand...

Here's a sample of my data:
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>
[TH="bgcolor: #c0c0c0"] Employee [/TH]
[TH="bgcolor: #c0c0c0"] Department [/TH]
[TH="bgcolor: #c0c0c0"] Day [/TH]
[TH="bgcolor: #c0c0c0"] WorkFromHome [/TH]
[TH="bgcolor: #c0c0c0"] AMend [/TH]
[TH="bgcolor: #c0c0c0"] PMstart [/TH]
[TH="bgcolor: #c0c0c0"] StartTime [/TH]
[TH="bgcolor: #c0c0c0"] AMBreak [/TH]
[TH="bgcolor: #c0c0c0"] Lunch1 [/TH]
[TH="bgcolor: #c0c0c0"] Lunch2 [/TH]
[TH="bgcolor: #c0c0c0"] Lunch3 [/TH]
[TH="bgcolor: #c0c0c0"] PMBreak [/TH]
[TH="bgcolor: #c0c0c0"] End [/TH]

</tbody>
 
Last edited:
Upvote 0
Here's what I got out for Jones, Bob:
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>
[TH="bgcolor: #c0c0c0"] Department [/TH]
[TH="bgcolor: #c0c0c0"] WorkFromHome [/TH]
[TH="bgcolor: #c0c0c0"] Employee [/TH]
[TH="bgcolor: #c0c0c0"] Day [/TH]
[TH="bgcolor: #c0c0c0"] Rank [/TH]
[TH="bgcolor: #c0c0c0"] IntervalStart [/TH]
[TH="bgcolor: #c0c0c0"] Working [/TH]
[TH="bgcolor: #c0c0c0"] FromHome [/TH]

</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>
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Automate a table showing the total number of times the numbers are less than 18 and greater than 18 for each time per day
Replies
5
Views
370
keranali
K
W
  • Solved
Greater than equal to Less than with time format formula
Replies
9
Views
4K
waqasgul
W
J
  • Question Question
Help with greater than, less than
Replies
3
Views
1K
johnb1979
J
W
  • Solved
Conditional formatting of invoice lists of varying lengths for presence of a given string
5 6 7
Replies
65
Views
5K
Alex Blakenburg
A
Ironman
  • Question Question
Msgbox when value for this year to date greater than this time last year
2
Replies
11
Views
2K
Ironman
Ironman

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

Share this page

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