Solved

Find the dates of a work cycle based off of pay date and shift

Posted on 2011-02-28
19
183 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:bvanscoy678
  • 7
  • 6
  • 3
  • +1
19 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34997392
Would a VBA code help?

Sid
0
 

Author Comment

by:bvanscoy678
ID: 34997447
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
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34997525
Quick question. Would 1-8-11 be a part of that dates as the start date is 01/09/11 ?

Sid
0
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 34997565
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,INDEX(('Figure Dates'!$D4='paydate list'!$E$2:$E$366)*('Figure Dates'!E$1='paydate list'!$F$2:$F$366),0),0))

place that in cell E4 and you can drag it accross and down


-Brandon
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34997569
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 list'!$D2:$D1000>=$B2-($D2="B"),IF('paydate list'!$D2:$D1000<=$C2,IF('paydate list'!$E2:$E1000=$D2,'paydate list'!$D2:$D1000))),COLUMNS($E2:E2)),""))

confirmed with CTRL+SHIFT+ENTER and copied across as far as required

see attached

regards, barry
26852449.xlsx
0
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 34997570
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 250 total points
ID: 34997597
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
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

Open in new window


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

Open in new window

File-A.xlsm
File-B.xlsm
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34997660
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
0
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 34997723
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
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:bvanscoy678
ID: 34998776
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
0
 

Author Comment

by:bvanscoy678
ID: 34998888
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.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34998915
Yes from H5 onwards.

Sid
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 250 total points
ID: 34999076
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 list'!$D2:$D1000>=$B2-1,IF('paydate list'!$D2:$D1000<=$C2,IF('paydate list'!$E2:$E1000=$D2,'paydate list'!$D2:$D1000))),COLUMNS($E2:E2)),""))

regards, barry
0
 

Author Comment

by:bvanscoy678
ID: 34999145
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
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34999159
Strange. This is what I get.

Sid
Untitled.jpg
0
 

Author Comment

by:bvanscoy678
ID: 34999232
I re download it after I grab the kid from the bus. Very interested in both solutions.
0
 

Author Closing Comment

by:bvanscoy678
ID: 34999538
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
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34999559
>>Both solutions will work perfect.

Brent: There was no rush :) You could have tested my solution first before awarding points to my post :)

Anyways. If you find any problems, just yell ;-)

Sid
0
 

Author Comment

by:bvanscoy678
ID: 34999712
No worries, both solutions are good. It will take me a few days to figure out which one to use at it suits the project. But, both are worth points.

thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Consolidate xl 2010 worksheets with text 2 24
Formula or Macro to determine variance 17 75
Freeze Panes Solution 6 28
Excel VBA When using VLookup 6 29
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now