Work schedule budget help

[b4ronin]

I need a formula or macro I enter a “start date” say 1/5/2022 use count +28 in a different cell I get 2/2/2022 use count +14 and get 2/16/2022 then repeat in the pattern of +28 / +14

this is how I got my work schedule for the year
( I’m a sailor )…I need to pull days worked per pay period which are 1st - 15th & 16th to end of month which is adaptable Sometimes I got 3 days sometimes 16 or I’m in 2 different months entirely the above gives me

1/5/2022 through the 15th = 10 days
Payday on 1/20/22
1/15/2022 through the 31st = 16 days
Payday 2/5/22
2/1/2022 through the 2nd = 2 days
Payday on 2/20/22 (no more days worked for tht pay period)

Trying to create a sheet to help keep me on top of my finances at a glance currently I manually count the days. I just started really using excel and found I genuinely like it and can see how powerful it is found tons of tutorials but not sure how to even phrase the above into an appropriate question.

Thank you In advance for any help

 

[Toadstool]

Hi B4Ronin,

I'm struggling to see the significance of 28 or 14 days, how the Pay Day is calculated and how you want formulae to present results.

I will note that if you work 1st through 15th of January then you've worked 11 days, not 10. You must count the first and last day so subtract to get the difference but then add one to get the number of days. e.g.

B4Ronin.xlsx
	A	B	C	D
1	Start	End	Days Worked	Pay Day
2	1/5/2022	1/15/2022	11	1/20/2022
3	1/15/2022	1/31/2022	17	2/5/2022
4	2/1/2022	2/2/2022	2	2/20/2022
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=(B2-A2)+1

 

[GraH]

Would it be something like this, for the sequence + 28/14 days? (Sorry, I'm without XL2BB for the moment)

=B2+LOOKUP(MOD(ROW();2);{0;1};{14;28})

 

[b4ronin]

Hi B4Ronin,

I'm struggling to see the significance of 28 or 14 days, how the Pay Day is calculated and how you want formulae to present results.

I will note that if you work 1st through 15th of January then you've worked 11 days, not 10. You must count the first and last day so subtract to get the difference but then add one to get the number of days. e.g.

B4Ronin.xlsx
	A	B	C	D
1	Start	End	Days Worked	Pay Day
2	1/5/2022	1/15/2022	11	1/20/2022
3	1/15/2022	1/31/2022	17	2/5/2022
4	2/1/2022	2/2/2022	2	2/20/2022
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=(B2-A2)+1

The significance of 28 & 14 is I work on a ship 28 days straight no weekends and go home 14 days my pay dates are always the 5th (16th -end ) and the 20th ( 1st -15th ) in above example I have 3 paydays spanning 2 months for a single “schedule” that is 28 days I’d like 2-3 numbers from the start - end date so I can see what I have made and if I need to pay it forward for the following months bills if you follow my schedule I have zero income on Feb 20th and 12 days for March 5th which means all my bills are late unfortunately a traditional budget sheet won’t work for me I don’t work a regular job

 

[b4ronin]

Would it be something like this, for the sequence + 28/14 days? (Sorry, I'm without XL2BB for the moment)

=B2+LOOKUP(MOD(ROW();2);{0;1};{14;28})

View attachment 49204

I’m new to excel what I did was set a box as my start date ( ON ) and another end date ( off ) +28
( on ) + 14 and kept repeating until I had 13 rows of on and off

 

[b4ronin]

I’m new to excel what I did was set a box as my start date ( ON ) and another end date ( off ) +28
( on ) + 14 and kept repeating until I had 13 rows of on and off

 

[GraH]

I’m new to excel what I did was set a box as my start date ( ON ) and another end date ( off ) +28
( on ) + 14 and kept repeating until I had 13 rows of on and off

Like this you mean?

On	Off
05/01/2022​	02/02/2022​		input date	=B2+28
16/02/2022​	16/03/2022​		=C2+14	=B3+28
30/03/2022​	27/04/2022​		=C3+14	=B4+28
11/05/2022​	08/06/2022​		=C4+14	=B5+28
22/06/2022​	20/07/2022​		=C5+14	=B6+28
03/08/2022​	31/08/2022​		=C6+14	=B7+28
14/09/2022​	12/10/2022​		=C7+14	=B8+28
26/10/2022​	23/11/2022​		=C8+14	=B9+28
07/12/2022​	04/01/2023​		=C9+14	=B10+28

 

[b4ronin]

Like this you mean?

On	Off
05/01/2022​	02/02/2022​		input date	=B2+28
16/02/2022​	16/03/2022​		=C2+14	=B3+28
30/03/2022​	27/04/2022​		=C3+14	=B4+28
11/05/2022​	08/06/2022​		=C4+14	=B5+28
22/06/2022​	20/07/2022​		=C5+14	=B6+28
03/08/2022​	31/08/2022​		=C6+14	=B7+28
14/09/2022​	12/10/2022​		=C7+14	=B8+28
26/10/2022​	23/11/2022​		=C8+14	=B9+28
07/12/2022​	04/01/2023​		=C9+14	=B10+28

Basically yea

Like this you mean?

On	Off
05/01/2022​	02/02/2022​		input date	=B2+28
16/02/2022​	16/03/2022​		=C2+14	=B3+28
30/03/2022​	27/04/2022​		=C3+14	=B4+28
11/05/2022​	08/06/2022​		=C4+14	=B5+28
22/06/2022​	20/07/2022​		=C5+14	=B6+28
03/08/2022​	31/08/2022​		=C6+14	=B7+28
14/09/2022​	12/10/2022​		=C7+14	=B8+28
26/10/2022​	23/11/2022​		=C8+14	=B9+28
07/12/2022​	04/01/2023​		=C9+14	=B1

 

[b4ronin]

Pretty much yea I posted a screen shot showing how crazy my pay dates can get using what you did per 28 days I can have as many as 3 pay dates… using what you did I’m trying to get the results if it’s even possible to show 2-3 pay dates based on the schedule of 28 days worked if I can get that part done I’d try for holidays to populate a different group of cells as well but that’s a later question