Need a formula that queries data by date

[Newtonus_Prime]

Good morning,

I have been struggling with pulling data from 1 sheet by date and am not sure how to write the formula. On the attached sheet, I am attempting to duplicate some formulas that are currently pulling data by which machine the Operator was using, but I need to pull the exact data by date instead. The current formulas are in cells K156 through P165 on tab "Crystal Anderson". I have added an area next to this where I would like to view the new data (B154 through H162). This is a sheet that is used weekly, so I will not have more than 7 days to review, but I need to be able to get the data without manually entering the dates on each tab every week if possible. I am hoping for some type of Query that pulls based off of the dates entered from the sheet. I hope this isn't confusing- please let me know if it is!

Thank you in advance,

Adam

[Pete_UK]

I'm not sure exactly what you want, but you can obtain the earliest date within your data by using this formula in cell B156:

=MINIFS($G$2:$G$151,$G$2:$G$151,"<>")

You can then use this formula in B157:

=B156+1

and copy this down to get the seven dates for that week. Of course, you need to format those cells as dates in the style you prefer, and apply a contrasting font colour so that you can see the values.

You can use this formula in C156:

=SUMIF($G$2:$G$151,$B156,$K$2:$K$151)

and this in D156:

=SUMIF($G$2:$G$151,$B156,$T$2:$T$151)

I think you know how to calculate the other columns in that table.

Hope this helps.

Pete

[Newtonus_Prime]

Hi Pete.,

That works great. I did come across another problem that I didn't realize when thinking about this. Is there a way to break up the time by 6AM-6PM and 6PM to 6AM? I have a 2nd shift that starts at 6PM and works through 6AM, which throws a wrench in this for half of my employees. I do appreciate your help, and didn't realize my other issue until I plugged the formulas into my 2nd shift Employees.

Thank you,
Adam

[Pete_UK]

I'm not sure how you distinguish between the two shifts.

Do you mean to pick up from the Start time and Stop time (Cols I and J) and determine which shift each job relates to, or do you have another column which is used to record which shift the job was handled by?

If the real file differs from your sample file I will need to see this.

Pete

[Newtonus_Prime]

I appreciate your help! The tab that you assisted on was for a 1st shift Employee, so the formula works for her because all of her shift is on the same day. My 2nd Shift Employees (example: Adrian Saucedo-Mendoza) starts at 6PM and works until 6AM, so getting his data would require using columns I and J and keeping data that is marked after midnight until 6AM with the previous date. This may be confusing because I struggled writing it. I think you had it right in your second sentence- "Do you mean to pick up from the Start time and Stop time (Cols I and J) and determine which shift each job relates to". Again, I appreciate your help!

Thank you,
Adam

[Pete_UK]

I think I understand what you are saying, but you seem to already know if the data is from the first shift or the second, so couldn't you just use another column (next to V) and pre-fill this with the shift code?

It would help if you can supply another sample file but this time filled with data from a worker on the 2nd shift, so I can check out different ways of handling this.

Pete

[Newtonus_Prime]

I am not opposed to adding another column I don’t know how to automate the shift tag that would correct my data for the correct shift though. I didn’t think about it that way.

[Pete_UK]

The sheet for Adrian Saucedo-Mendoza is protected, so I can't try things out in that sheet.

Pete

[Newtonus_Prime]

Sorry about that. I have removed the protection. The password is "schedule" without quotations in case you need to see any other sheets.

Thank you,
Adam

[Pete_UK]

The first few entries for Adrian (i.e. rows 3 to 11) all take place between midnight and 6:00am on 15th Jan. Are you saying that you want these to be treated as having occurred on 14th Jan, and if so, shouldn't those rows of data be included in the previous week's data when the first half of the shift took place?

I don't know how you are extracting the data for these sheets, but you would have to take account of this to ensure that the second half of the shift was included along with the first half.

The reason why I was suggesting using another column earlier is that I can't change any of the data directly with a formula, but I could have something like this in W2:

=IF(G2="","",IF(AND(I2>0,I2<0.25),G2-1,G2))

If you format this as a date you will be able to compare it directly with column G where your other dates are. You will see that where the start time is greater than zero (to avoid including blanks) and less than 0.25 (equivalent to 6:00am) then the date is decremented, otherwise it is left the same. You can see further down the column that the dates which relate to the evening shift (6:00pm to midnight) on 15th Jan remain the same, but those relating to early morning (midnight to 6:00am) on 16th Jan (rows 33 to 36) are changed to 15th Jan. I think this is what you wanted.

Note, however, that the formula in B156 will need to be modified to look at column W instead of column G.

Note also, that you can always hide column W if you want the sheet to look the same as it did.

Hope this helps.

Pete

[Newtonus_Prime]

Hi Pete,

I have updated the sheet (new file uploaded) and added some new jobs from Adrian (2nd shift). I did notice that the first line that shows BELT CLEANING is showing 1/15/24 in column W and the lines below are showing 1/14/24. I think that this is really close to what I need. The entries that Adrian has as 1/15/24 would have been for 1/14/24, which everything after midnight is correct, it was just the 6:01 PM to 12:00 AM on row 2 that is showing 1/15/24 in column W. I really appreciate your help on this and see that it is very close!

Thanks again,
Adam

[Pete_UK]

You didn't have a date in column G in the earlier file that you had posted, so I don't know why you are showing a date in cell G2 anyway (same applies to G12).

Other than the fact that the date in W2 is now out of sequence compared to W3 and below, I don't see what the problem is. The formula is meant to subtract 1 from the date in G IF the Start Time is in the later half of the shift (i.e. midnight to 6:00am). That particular row is for the first half of the shift, so the date doesn't get changed.

But, if you want it to subtract 1 from the date whenever "Cleaning" appears in column A, then you can change the formula in W2 to this:

=IF(G2="","",G2-IF(OR(AND(I2>0,I2<0.25),COUNTIF(A2,"*CLEANING*")),1,0))

Incidentally, if you have times wrapping around midnight, then a more robust calculation for the difference in K2 would be:

=((J2-I2)+(J2<I2))*24

Note that this is different than the result you were showing, so I'm not sure what knock-on effects this will produce.

By the way, I forgot to mention that formulae in cells C156:H162 will also need to be changed if they refer to column G instead of column W.

Hope this helps.

Pete

[Newtonus_Prime]

Thank you Pete_UK! That works perfectly.

Adam

[Pete_UK]

That's good to hear - thanks for feeding back.

Pete