Make Your Microsoft Dynamics Investment Count & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Find the dates of a work cycle based off of pay date and shift
Posted on 2011-02-28
I thought I could do this with Vlookup, but not so sure.
If I know the pay date and the shift, is it possible to have a formula figure what dates that shift will work during that paydate?
Example: if I enter the pay date of 1/28/11 and also what shift I am looking for (we have 3-4 shifts what rotate), will
vlookup work to find the individual dates of the pay period? The pay period always starts on a Saturday at midnight and ends on a Saturday at midnight. So the beginning of our pay can be the 7 hours (midnight to 7) or if the last day lands on a Saturday, it would be 17 hours (0700-midnight).
So in this example if I choose the B shift, it would return the dates of 1-8-11 (last 7), 1-10-11, 1-12-11, 1-14-11 and 1-21-11.
I built the beginning of the list what I would try to use to figure this out.
I am thinking I will need something different than a vlookup, because it is based off of the pay date, pay periods and shift.
any help in the right direction is appreciated.
Thanks. EXPERT-FIND-DATE-BOOK.xlsx
If I know the pay date and the shift, is it possible to have a formula figure what dates that shift will work during that paydate?
Example: if I enter the pay date of 1/28/11 and also what shift I am looking for (we have 3-4 shifts what rotate), will
vlookup work to find the individual dates of the pay period? The pay period always starts on a Saturday at midnight and ends on a Saturday at midnight. So the beginning of our pay can be the 7 hours (midnight to 7) or if the last day lands on a Saturday, it would be 17 hours (0700-midnight).
So in this example if I choose the B shift, it would return the dates of 1-8-11 (last 7), 1-10-11, 1-12-11, 1-14-11 and 1-21-11.
I built the beginning of the list what I would try to use to figure this out.
I am thinking I will need something different than a vlookup, because it is based off of the pay date, pay periods and shift.
any help in the right direction is appreciated.
Thanks. EXPERT-FIND-DATE-BOOK.xlsx
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
7
6
3- +1
19 Comments
VBA Code is fine. I just don't know how to do very much VBA.
Off to a meeting. I'll keep reading and check back when meeting is over.
thanks
Off to a meeting. I'll keep reading and check back when meeting is over.
thanks
If you change the headers on the 'Figure Dates' tab to DAY 1, DAY 2 etc. to match column F on the 'paydate list' tab, this formula will do what you need:
=INDEX('paydate list'!$D$2:$D$366,MATCH(1,
place that in cell E4 and you can drag it accross and down
-Brandon
=INDEX('paydate list'!$D$2:$D$366,MATCH(1,
place that in cell E4 and you can drag it accross and down
-Brandon
Hello bvanscoy678,
You can do this with formulas if you want.......
I think the tricky part here is picking up a shift that starts before the pay period starts. How can we know from the data provided that a shift that starts on 8th Jan crosses midnight into 9th Jan and is therefore the first B shift of the period? Which shifts will cross midnight, is it only B?
Assuming that it is then you could use this "array formula" in E2
=IFERROR(SMALL(IF('paydate
confirmed with CTRL+SHIFT+ENTER and copied across as far as required
see attached
regards, barry
26852449.xlsx
You can do this with formulas if you want.......
I think the tricky part here is picking up a shift that starts before the pay period starts. How can we know from the data provided that a shift that starts on 8th Jan crosses midnight into 9th Jan and is therefore the first B shift of the period? Which shifts will cross midnight, is it only B?
Assuming that it is then you could use this "array formula" in E2
=IFERROR(SMALL(IF('paydate
confirmed with CTRL+SHIFT+ENTER and copied across as far as required
see attached
regards, barry
26852449.xlsx
See attached
Copy-of-EXPERT-FIND-DATE-BOOK-1.xlsx
Copy-of-EXPERT-FIND-DATE-BOOK-1.xlsx
If 1-8-11 shouldn't be a part then use File A and if it should then use File B.
All you need to do is Click on the command button.
Sid
Code Used - File A
Code Used - File B
File-B.xlsm
All you need to do is Click on the command button.
Sid
Code Used - File A
Private Sub CommandButton1_Click()
Dim aCell As Range
Dim SearchDate As Date, StartDate As Date, EndDate As Date
Dim lastRow As Long, i As Long, outputRow As Long
Range("H5:J" & Rows.Count).ClearContents
SearchDate = Range("I1").Value
Set aCell = Columns(1).Find(What:=SearchDate, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
StartDate = aCell.Offset(, 1).Value
EndDate = aCell.Offset(, 2).Value
lastRow = Range("D" & Rows.Count).End(xlUp).Row
For i = 2 To lastRow
If (Range("D" & i).Value = StartDate Or Range("D" & i).Value > StartDate) And _
(Range("D" & i).Value = EndDate Or Range("D" & i).Value < EndDate) Then
If Range("E" & i).Value = Range("I2").Value Then
outputRow = Range("H" & Rows.Count).End(xlUp).Row + 1
Range("H" & outputRow).Value = Range("D" & i).Value
Range("I" & outputRow).Value = Range("E" & i).Value
Range("J" & outputRow).Value = Range("F" & i).Value
End If
End If
Next i
Else
MsgBox "Not Found"
End If
End Sub
Code Used - File B
Private Sub CommandButton1_Click()
Dim aCell As Range
Dim SearchDate As Date, StartDate As Date, EndDate As Date
Dim lastRow As Long, i As Long, outputRow As Long
Range("H5:J" & Rows.Count).ClearContents
SearchDate = Range("I1").Value
Set aCell = Columns(1).Find(What:=SearchDate, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
StartDate = aCell.Offset(, 1).Value - 1
EndDate = aCell.Offset(, 2).Value
lastRow = Range("D" & Rows.Count).End(xlUp).Row
For i = 2 To lastRow
If (Range("D" & i).Value = StartDate Or Range("D" & i).Value > StartDate) And _
(Range("D" & i).Value = EndDate Or Range("D" & i).Value < EndDate) Then
If Range("E" & i).Value = Range("I2").Value Then
outputRow = Range("H" & Rows.Count).End(xlUp).Row + 1
Range("H" & outputRow).Value = Range("D" & i).Value
Range("I" & outputRow).Value = Range("E" & i).Value
Range("J" & outputRow).Value = Range("F" & i).Value
End If
End If
Next i
Else
MsgBox "Not Found"
End If
End Sub
File-B.xlsm
Brandon,
I don't think you can do it that way. I think the idea is that all the shift dates for the specific period need to be shown, so you need to take into account the dates in B2 and C2 which define the start and end of the period - that's what my suggestion does....
regards, barry
I don't think you can do it that way. I think the idea is that all the shift dates for the specific period need to be shown, so you need to take into account the dates in B2 and C2 which define the start and end of the period - that's what my suggestion does....
regards, barry
yea i saw that after i posted my response. It looks like you guys have provided sufficent answers so i'll step down from this one, thanks for pointing it out though.
-Brandon
-Brandon
Barry,
Sorry for not providing enough deails. Our shift start at 7am and the run until 7am the next day. it is a 24 hour shift.
I am going to study the array formula and also Sid's code. I'll check back soon.
Thanks
Sorry for not providing enough deails. Our shift start at 7am and the run until 7am the next day. it is a 24 hour shift.
I am going to study the array formula and also Sid's code. I'll check back soon.
Thanks
Sid,
I tried the B file. I entered the dates and the shift and hit update. is it suppose to populate the boxes right underneath?
KnutsonBM,
I seen you used an index file. I entered the data, but not sure where it changes. Bit lost with index.
Barry,
The array formula seems to work. I need to run some different dates and see I gave you enough info.
Thanks.
I tried the B file. I entered the dates and the shift and hit update. is it suppose to populate the boxes right underneath?
KnutsonBM,
I seen you used an index file. I entered the data, but not sure where it changes. Bit lost with index.
Barry,
The array formula seems to work. I need to run some different dates and see I gave you enough info.
Thanks.
I made the formula only look at the day before the period start if the shift was B, if that could potentially be any shift then I'd remove that bit, so formula would be just
=IFERROR(SMALL(IF('paydate
regards, barry
=IFERROR(SMALL(IF('paydate
regards, barry
Sid,
I enter the pay date in I1 and the shift in I2 and hit update. Nothing happends.
I have the macro security to low.
Barry,
Yes, it could be any shift. A, B or C. Depends on where the rotation hits at the start of the pay period.
I'll continue to look at this. Both are way over my knowledge, but still interested in learning from this.
Thanks
I enter the pay date in I1 and the shift in I2 and hit update. Nothing happends.
I have the macro security to low.
Barry,
Yes, it could be any shift. A, B or C. Depends on where the rotation hits at the start of the pay period.
I'll continue to look at this. Both are way over my knowledge, but still interested in learning from this.
Thanks
Both solutions will work perfect. I am going to take some time to find out which one will work the best with the project. Way over my head, but very interesting.
thanks
Brent
thanks
Brent
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month7 days, left to enroll
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.