Bi-Weekly date problem :(

T

Ticcer

New Member
Joined
Aug 2, 2011
Messages
37
In C1 I have the beginning date of the first pay period an employee commenced work, e.g. 18/09/2015. In C2 I have the end date of that first pay period, e.g. 01/10/2015. I thought these would be useful as formula helpers.


The records begin at row 6, with the date the employee began working at C6, e.g. 28/09/2015, C7 = 29/09/2015, C8 = 30/09/2015, C9 = 01/10/2015 and so on. as shown below.

I'd like a formula in the I column (Pay Period) to check the date in the C column (Date Worked) and determine the pay period number for the employee, e.g. all dates in column C between (and including) 18/09/2015 and 01/10/2015 should return a '1' result in column I (even though the employee began after the 18/09/2015). All dates in column C between (and including) 02/10/2015 and 15/10/2015 should return the number '2' to the formula in column I, and so on.

Basically, as shown below, the number in column I increments by 1 every 14 days, but the employee started in the middle of the first pay period, so there are only 4 cells in column I that have the number 1.


I can't figure out for the life of me how to account for that part period in a formula.


Sample data set is below The "Store" column is B

StoreDate WorkedDay WorkedTime StartTime FinishUnpaid BreakHours WorkedPay PeriodPay Day
Store 128/09/2015Monday9:00 AM5:30 PM0.581
Store 129/09/2015Tuesday9:00 AM5:30 PM0.581
Day Off30/09/2015Wednesday9:00 AM5:30 PM0.581
Store 11/10/2015Thursday9:00 AM5:30 PM0.581Yes
Store 12/10/2015Friday9:00 AM5:30 PM0.582
Day Off3/10/2015SaturdayOffOff02
Day Off4/10/2015SundayOffOff02
Day Off5/10/2015Monday9:00 AM5:30 PM0.582
Store 16/10/2015Tuesday9:00 AM5:30 PM0.582
Store 17/10/2015Wednesday9:00 AM5:30 PM0.582
Store 18/10/2015Thursday9:00 AM5:00 PM0.57.52
Store 19/10/2015Friday9:00 AM5:30 PM0.582
Store 110/10/2015SaturdayOffOff02
Store 111/10/2015SundayOffOff02
Day Off12/10/2015MondayOffOff02
Store 113/10/2015Tuesday9:00 AM5:30 PM0.582
Store 114/10/2015Wednesday9:00 AM5:30 PM0.582
Store 115/10/2015Thursday9:00 AM6:30 PM0.592Yes
Store 116/10/2015Friday9:00 AM5:30 PM0.583
Store 117/10/2015SaturdayOffOff03
Store 118/10/2015SundayOffOff03
Store 119/10/2015Monday9:00 AM5:30 PM0.583
Store 120/10/2015Tuesday9:00 AM5:30 PM0.583
Store 121/10/2015Wednesday9:00 AM5:30 PM0.583
Store 122/10/2015Thursday9:00 AM5:00 PM0.57.53
Store 123/10/2015Friday9:00 AM5:30 PM0.583
Day Off24/10/2015SaturdayOffOff03
Day Off25/10/2015SundayOffOff03
Store 126/10/2015MondayOffOff0.53
Store 127/10/2015TuesdayOffOff0.53
Store 128/10/2015Wednesday9:00 AM5:30 PM0.583
Store 129/10/2015Thursday9:00 AM5:00 PM0.57.53Yes

<tbody>
</tbody>


Any help would be much appreciated as it's been a few years since I picked up Excel and I'm way rusty. Using Excel 2010.
 
Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Hi,

This is one way.

I2 formula copied down, start date of 18/09/2015 hard coded in formula.
L2 formula copied down, start date in M2 used as reference in formula.


Book1
BCDEFGHIJKLM
1StoreDate WorkedDay WorkedTime StartTime FinishUnpaid BreakHours WorkedPay PeriodPay DayPay PeriodStart Date
2Store 128/09/2015Monday9:00 AM5:30 PM0.581118/09/2015
3Store 129/09/2015Tuesday9:00 AM5:30 PM0.5811
4Day Off30/09/2015Wednesday9:00 AM5:30 PM0.5811
5Store 101/10/2015Thursday9:00 AM5:30 PM0.581Yes1
6Store 102/10/2015Friday9:00 AM5:30 PM0.5822
7Day Off03/10/2015SaturdayOffOff022
8Day Off04/10/2015SundayOffOff022
9Day Off05/10/2015Monday9:00 AM5:30 PM0.5822
10Store 106/10/2015Tuesday9:00 AM5:30 PM0.5822
11Store 107/10/2015Wednesday9:00 AM5:30 PM0.5822
12Store 108/10/2015Thursday9:00 AM5:00 PM0.57.522
13Store 109/10/2015Friday9:00 AM5:30 PM0.5822
14Store 110/10/2015SaturdayOffOff022
15Store 111/10/2015SundayOffOff022
16Day Off12/10/2015MondayOffOff022
17Store 113/10/2015Tuesday9:00 AM5:30 PM0.5822
18Store 114/10/2015Wednesday9:00 AM5:30 PM0.5822
19Store 115/10/2015Thursday9:00 AM6:30 PM0.592Yes2
20Store 116/10/2015Friday9:00 AM5:30 PM0.5833
21Store 117/10/2015SaturdayOffOff033
22Store 118/10/2015SundayOffOff033
23Store 119/10/2015Monday9:00 AM5:30 PM0.5833
24Store 120/10/2015Tuesday9:00 AM5:30 PM0.5833
25Store 121/10/2015Wednesday9:00 AM5:30 PM0.5833
26Store 122/10/2015Thursday9:00 AM5:00 PM0.57.533
27Store 123/10/2015Friday9:00 AM5:30 PM0.5833
28Day Off24/10/2015SaturdayOffOff033
29Day Off25/10/2015SundayOffOff033
30Store 126/10/2015MondayOffOff0.533
31Store 127/10/2015TuesdayOffOff0.533
32Store 128/10/2015Wednesday9:00 AM5:30 PM0.5833
33Store 129/10/2015Thursday9:00 AM5:00 PM0.57.53Yes3
Sheet126
Cell Formulas
RangeFormula
I2=ROUNDUP((C2-DATE(2015,9,18)+1)/14,0)
L2=ROUNDUP((C2-M$2+1)/14,0)
 
Upvote 0
Thanks Jtakw. I'll give that a go when I get home. I don't mind the extra column... basically just want something that works lol. Cheers muchly.

T
 
Upvote 0
Ticcer said:
Thanks Jtakw. I'll give that a go when I get home. I don't mind the extra column... basically just want something that works lol. Cheers muchly.

T
Click to expand...

No, No....There's no Extra Column, I'm giving you a Choice of hard-coding the Start Date in the formula OR using a Cell Reference that you can Change easily.

Just choose one or the other, Not both...

Let me know if you need more clarification.
 
Upvote 0
jtakw said:
No, No....There's no Extra Column, I'm giving you a Choice of hard-coding the Start Date in the formula OR using a Cell Reference that you can Change easily.

Just choose one or the other, Not both...

Let me know if you need more clarification.
Click to expand...

Ah! I see. Sorry. didn't read it properly... too much going on at work lol.

Thanks for clarifying.

T
 
Upvote 0
Modified it slightly to work without hard coding date with no extra column:

First row of data is 6 with first date at C6, Start Date is at C1

=ROUNDUP((C6-DATE(YEAR($C$1),MONTH($C$1),DAY($C$1))+1)/14,0)

Works well. Many thanks again.

T
 
Upvote 0
You're welcome, glad you got it working.

But I Never suggested an Extra Column. Thought you understood.

Also, you stated start date is 18/09/2015, C1 (may be you mean C2), is 28/09/2015
 
Last edited:
Upvote 0
Ticcer said:
Modified it slightly to work without hard coding date

Start Date is at C1

=ROUNDUP((C6-DATE(YEAR($C$1),MONTH($C$1),DAY($C$1))+1)/14,0)

Works well. Many thanks again.

T
Click to expand...

Just curious, if this version of the formula is working, why can't it just be:

=ROUNDUP((C6-$C$1+1)/14,0)

as I suggested in post #2 , L2 formula, Cell reference version?
 
Upvote 0
Correct. I screwed up the dates. Start date is 28/9/2015 not 18/9/2015. So =ROUNDUP((C6-C$1+11)/14,0) works in column I :)

Excel 2010
ABCDEFGHI
1First Pay Period Start28/09/2015
2First Pay Period End1/10/2015
3
4
5Pay DayStoreDate WorkedDay WorkedTime StartTime FinishUnpaid BreakHours WorkedPay Period
6Store 128/09/2015Monday9:00 AM5:30 PM0.581
7Store 129/09/2015Tuesday9:00 AM5:30 PM0.581
8Day Off30/09/2015Wednesday9:00 AM5:30 PM0.581
9YesStore 11/10/2015Thursday9:00 AM5:30 PM0.581
10Store 12/10/2015Friday9:00 AM5:30 PM0.582
11Day Off3/10/2015SaturdayOffOff02
12Day Off4/10/2015SundayOffOff02
13Day Off5/10/2015Monday9:00 AM5:30 PM0.582
14Store 16/10/2015Tuesday9:00 AM5:30 PM0.582
15Store 17/10/2015Wednesday9:00 AM5:30 PM0.582
16Store 18/10/2015Thursday9:00 AM5:00 PM0.57.52
17Store 19/10/2015Friday9:00 AM5:30 PM0.582
18Store 110/10/2015SaturdayOffOff02
19Store 111/10/2015SundayOffOff02
20Day Off12/10/2015MondayOffOff02
21Store 113/10/2015Tuesday9:00 AM5:30 PM0.582
22Store 114/10/2015Wednesday9:00 AM5:30 PM0.582
23YesStore 115/10/2015Thursday9:00 AM6:30 PM0.592
24Store 116/10/2015Friday9:00 AM5:30 PM0.583
25Store 117/10/2015SaturdayOffOff03
26Store 118/10/2015SundayOffOff03
27Store 119/10/2015Monday9:00 AM5:30 PM0.583
28Store 120/10/2015Tuesday9:00 AM5:30 PM0.583
29Store 121/10/2015Wednesday9:00 AM5:30 PM0.583
30Store 122/10/2015Thursday9:00 AM5:00 PM0.57.53
31Store 123/10/2015Friday9:00 AM5:30 PM0.583
32Day Off24/10/2015SaturdayOffOff03
33Day Off25/10/2015SundayOffOff03
34Store 126/10/2015MondayOffOff0.53
35Store 127/10/2015TuesdayOffOff0.53
36Store 128/10/2015Wednesday9:00 AM5:30 PM0.583
37YesStore 129/10/2015Thursday9:00 AM5:00 PM0.57.53

<tbody>
</tbody>

​
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Convert and copy to Date
Replies
5
Views
110
Micron
M
R
  • Solved
Read through XML file
Replies
1
Views
242
rjtaylor
R
C
  • Question
Condition calculation
Replies
1
Views
143
shknbk2
S
Z
  • Solved
Formula to find 'Part of the Day' from the Time Column
Replies
8
Views
305
Zee996
Z
B
  • Solved
Firing Pete Best
Replies
2
Views
321
BloodyBill
B

Forum statistics

Threads
1,215,047
Messages
6,122,858
Members
449,096
Latest member
Erald

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top