Bill wants to know: "Did a new shift start between Time 1 and Time 2? It sounds like such an easy formula, and it probably is, but I sure had a tough time puzzling this formula out. Do you have something easier?" Today, in Episode #1452, MrExcel looks at working with time and day changes.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1452: is it a new shift?
Okay, this one is just a doozy and I-- I'm wondering if someone out there is going to have an easier formula.
In fact, I'm sure someone's going to have an easier formula.
I don't know why this is so hard.
This is-- Lieutenant Kevin sent this in.
We have a shift and we've logged the time that the last change happen on that shift.
We know that a new day starts at a different time depending on which shift we have and we know what time it is now.
All right, and the question is, I need a formula out here that says: is it a new day?
Has a new shift started?
So, for days, once we get past 6 p.m. then it's a new day.
So, here we have the 13th and the 13th at 9 a.m. and almost 6 p.m.
So, no, it's not a new day yet.
Then down here we go from yesterday at almost 6 p.m.
We haven't-- have we crossed 10 a.m.?
Yes, yes we have because it's a new day.
I was just trying to like-- these are words out here.
These aren't formula, so I'm just trying to figure out, is it a new day or not?
I’m trying to figure what the rules are.
So, I realized I kind of want to look out here at the number of days between.
How many days from here and here?
Because it might be something that happens once a week.
If it's two days or later-- if the span between here is two days or later, then it's a new today no matter what.
If it's the same day, if column B and column D have the same date, then I need to look and see if the B time is less than C and also the D time has to be less than C. Both of those have to be true for it to be a new day.
If it's the next day, if it's the next day, then one of two things has to be true.
Either the B time has to be less than C or the D time has to be greater than C for it to be a new day.
Now, when we have dates and times, how do we separate the date and time?
Well, we can use the I-N-T to figure out the date, but to figure out the time, the time, we have to take the MOD, and it's not of zero here, that's actually wrong.
That has to be the MOD of the date and time, comma, 1.
What that does is that chops off the integer portion and leaves us just the time tops.
So, that's an interesting way to get the date and time.
So, I ended up with this horrible, horrible formula out here.
Let's see if we can get it back into the screen so everyone can see it.
Okay, so, we ended up with this horrible formula out here that tries to take those words that I wrote and put it into a formula.
The first thing we do is we look at the date of column D minus the date of column B.
If that's more than one day, then it's a new day no matter what.
Otherwise, if it's today, if the date of D and the date of B are the same, if the difference is the same, then I have a big AND function here.
Check the time of B, see if it’s less than C. Also check the time of D and see if it’s C. So, that's if column B and column D are the same day.
Otherwise, if it's one day apart, then we need to do an OR and we end up with our trues and falses here.
Back in the days when I was an engineering student, we would do a kernel map, that’s spelled with a K-- a kernel map to try and figure out the least number of logic gates that would-- you would use here, but as I look at this, I can't simplify it any more than this.
I feel pretty stupid.
I'm-- I'm sitting here, I know I'm going to be in Florida next week, so I'm doing today's podcast and in a couple of minutes, I'm going to do tomorrow podcast.
I'm sure there's a dozen of you that are screaming that there's some easier way to do this.
Shoot me a note, bill@mrexcel.com and next week, I'll be here sheepishly admitting that I completely made this a lot harder than it is, but I need this formula for tomorrow's podcast.
So, stop back tomorrow and you'll see where we're headed with this using this horrible formula.
All right, hey, I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1452: is it a new shift?
Okay, this one is just a doozy and I-- I'm wondering if someone out there is going to have an easier formula.
In fact, I'm sure someone's going to have an easier formula.
I don't know why this is so hard.
This is-- Lieutenant Kevin sent this in.
We have a shift and we've logged the time that the last change happen on that shift.
We know that a new day starts at a different time depending on which shift we have and we know what time it is now.
All right, and the question is, I need a formula out here that says: is it a new day?
Has a new shift started?
So, for days, once we get past 6 p.m. then it's a new day.
So, here we have the 13th and the 13th at 9 a.m. and almost 6 p.m.
So, no, it's not a new day yet.
Then down here we go from yesterday at almost 6 p.m.
We haven't-- have we crossed 10 a.m.?
Yes, yes we have because it's a new day.
I was just trying to like-- these are words out here.
These aren't formula, so I'm just trying to figure out, is it a new day or not?
I’m trying to figure what the rules are.
So, I realized I kind of want to look out here at the number of days between.
How many days from here and here?
Because it might be something that happens once a week.
If it's two days or later-- if the span between here is two days or later, then it's a new today no matter what.
If it's the same day, if column B and column D have the same date, then I need to look and see if the B time is less than C and also the D time has to be less than C. Both of those have to be true for it to be a new day.
If it's the next day, if it's the next day, then one of two things has to be true.
Either the B time has to be less than C or the D time has to be greater than C for it to be a new day.
Now, when we have dates and times, how do we separate the date and time?
Well, we can use the I-N-T to figure out the date, but to figure out the time, the time, we have to take the MOD, and it's not of zero here, that's actually wrong.
That has to be the MOD of the date and time, comma, 1.
What that does is that chops off the integer portion and leaves us just the time tops.
So, that's an interesting way to get the date and time.
So, I ended up with this horrible, horrible formula out here.
Let's see if we can get it back into the screen so everyone can see it.
Okay, so, we ended up with this horrible formula out here that tries to take those words that I wrote and put it into a formula.
The first thing we do is we look at the date of column D minus the date of column B.
If that's more than one day, then it's a new day no matter what.
Otherwise, if it's today, if the date of D and the date of B are the same, if the difference is the same, then I have a big AND function here.
Check the time of B, see if it’s less than C. Also check the time of D and see if it’s C. So, that's if column B and column D are the same day.
Otherwise, if it's one day apart, then we need to do an OR and we end up with our trues and falses here.
Back in the days when I was an engineering student, we would do a kernel map, that’s spelled with a K-- a kernel map to try and figure out the least number of logic gates that would-- you would use here, but as I look at this, I can't simplify it any more than this.
I feel pretty stupid.
I'm-- I'm sitting here, I know I'm going to be in Florida next week, so I'm doing today's podcast and in a couple of minutes, I'm going to do tomorrow podcast.
I'm sure there's a dozen of you that are screaming that there's some easier way to do this.
Shoot me a note, bill@mrexcel.com and next week, I'll be here sheepishly admitting that I completely made this a lot harder than it is, but I need this formula for tomorrow's podcast.
So, stop back tomorrow and you'll see where we're headed with this using this horrible formula.
All right, hey, I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
