Debrief
I'm putting together a workbook that checks the companies scheduling is in compliance with a few working standards regarding time, it seemed like an easy task at first but my formula seems to work sometimes but not others. I haven't worked with Time before in excel so it's a little unknown to me.
What I am trying to do
Create a formula that checks that there are 11 hours between the end time of the first shift and the start time of the second shift.
So if an Employee ends their Monday at 9:00pm, The earliest they should be working will be Tuesday at 8:00am.
My Code
I had thought something like this would work, but it only returns a value when the times are both "AM" or "PM"
Where E8 = Start time of the next shift and F7 = End time of the current shift
Workbook Sample
*I didn't bother including the column with my formula from before as it is currently not working
<tbody>
</tbody>
Too Long Didn't Read
I need to write a formula that will tell me how much time is between the end time of the first shift and the start time of the next shift.
Thank you in advance
I'm putting together a workbook that checks the companies scheduling is in compliance with a few working standards regarding time, it seemed like an easy task at first but my formula seems to work sometimes but not others. I haven't worked with Time before in excel so it's a little unknown to me.
What I am trying to do
Create a formula that checks that there are 11 hours between the end time of the first shift and the start time of the second shift.
So if an Employee ends their Monday at 9:00pm, The earliest they should be working will be Tuesday at 8:00am.
My Code
I had thought something like this would work, but it only returns a value when the times are both "AM" or "PM"
Code:
=TEXT(E8-F7,"h:mm")Workbook Sample
*I didn't bother including the column with my formula from before as it is currently not working
| Employee # (C) | Date (D) | Start (E) | End (F) | Duration (G) | Site (H) |
| 1204 | 25-Apr-2016 | 7:15:00 AM | 8:45:00 AM | 1:30 | CCAC |
| 1204 | 25-Apr-2016 | 9:00:00 AM | 12:00:00 PM | 3:00 | OUT |
| 1204 | 27-Apr-2016 | 10:00:00 AM | 11:00:00 AM | 1:00 | OUT |
| 1204 | 27-Apr-2016 | 12:00:00 PM | 1:00:00 PM | 1:00 | OUT |
| 1204 | 28-Apr-2016 | 12:00:00 PM | 1:00:00 PM | 1:00 | OUT |
| 1204 | 29-Apr-2016 | 10:00:00 AM | 11:00:00 AM | 1:00 | OUT |
| 1204 | 29-Apr-2016 | 12:00:00 PM | 1:00:00 PM | 1:00 | OUT |
| 1204 | 02-May-2016 | 7:15:00 AM | 8:45:00 AM | 1:30 | CCAC |
| 1204 | 02-May-2016 | 9:00:00 AM | 12:00:00 PM | 3:00 | OUT |
| 1204 | 04-May-2016 | 10:00:00 AM | 11:00:00 AM | 1:00 | OUT |
| 1204 | 04-May-2016 | 12:00:00 PM | 1:00:00 PM | 1:00 | OUT |
| 1204 | 06-May-2016 | 10:00:00 AM | 11:00:00 AM | 1:00 | OUT |
| 1204 | 06-May-2016 | 12:00:00 PM | 1:00:00 PM | 1:00 | OUT |
<tbody>
</tbody>
Too Long Didn't Read
I need to write a formula that will tell me how much time is between the end time of the first shift and the start time of the next shift.
Thank you in advance
