Updating Date Intervals VBA or Formulas

[dhosi439]

I'm trying to make a date interval legend in a userform. I don't have any issue with getting values into text boxes, I just can't rap my head around making the updating date interval into code or sheet formulas, and causing the code or formulas to update every new pay period.

Example:

I use four intervals bases on the pay weeks. For example this month pay weeks are 7/8 and 7/22, 14 days apart or two weeks. So four intervals into the future would yield this:

1Due - 7/8 to 7/22
2Due - 7/22 to 8/5
3Due - 8/5 to 8/19
4Due - 8/19 to 9/2

When 7/8 comes the legend above would update to the following:

1Due - 7/22 to 8/5
2Due - 8/5 to 8/19
3Due - 8/19 to 9/2
4Due - 9/2 to 9/16

Then when 7/22 comes the legend would be:

1Due - 8/5 to 8/19
2Due - 8/19 to 9/2
3Due - 9/2 to 9/16
4Due - 9/16 to 9/30

and so on.

I prefer to get this working in VBA, but I am fine with using data in a worksheet if this is a simpler task as I have a worksheet for data information to be used elsewhere in the workbook. This doesn't seem like it would be a very difficult task, but I do not have a strong grasp on dates in VBA or formula, other then adding and subtracting dates. If required the first pay day of this year was 1/7/10.

Any help is appreciated.

 

[dhosi439]

Can anyone help?

 

[ashleywanless]

if youre to and from dates are in seperate cells this can be done in a worksheet by writing some sheet code so when the date in the cell is reached it throws new values into the cells using a =today()+7 or something similar. you could then concatenate this back into one cell if required.

can you post me an example if the userform and ill think about how to do it for that

 

[dhosi439]

Okay, this is what I have so far.

The red box, for now, is the manually entered first pay of the year, I would like this to be automatically updated, but am not sure on a formula to output the first pay of the year. I do know that if you add 364 to the current first pay in the red box the new years first pay is the answer. The rest of the date values in row 2 and 3 are updated automatically by adding 14 to the previous date up until the last pay of the year. This then restarts below in cell B14 by adding 14 to the last pay of the previous year, ultimately there is no need for the rest of that year to be calculated, as I'll only need the first pay of the next year for the formula in the blue box to calculate correctly.

The formula in the blue box is:

=IF(TODAY()<C2,B2,IF(TODAY()<D2,C2,IF(TODAY()<E2,D2,IF(TODAY()<F2,E2,IF(TODAY()<G2,F2,IF(TODAY()<H2,G2,IF(TODAY()<I2,H2,IF(TODAY()<J2,I2,IF(TODAY()<K2,J2,IF(TODAY()<L2,K2,IF(TODAY()<M2,L2,IF(TODAY()<N2,M2,IF(TODAY()<B3,N2,IF(TODAY()<C3,B3,IF(TODAY()<D3,C3,IF(TODAY()<E3,D3,IF(TODAY()<F3,E3,IF(TODAY()<G3,F3,IF(TODAY()<H3,G3,IF(TODAY()<I3,H3,IF(TODAY()<J3,I3,IF(TODAY()<K3,J3,IF(TODAY()<L3,K3,IF(TODAY()<M3,L3,IF(TODAY()<N3,M3,IF(TODAY()<B14,N3,"ERROR"))))))))))))))))))))))))))

And then I add 14 to create the rest of the interval values beside the blue box.

So what I would like to get out of this at this point is help creating a formula to represent the first pay of the year in the red box, or VBA to output these values into a user form of text boxes. Note** I already know how to put values into text boxes, I need code for creating the dates, as I am very unfamiliar with dates in VBA.

I realize the VBA is going to need a bit more coding to create something like this, and it will be something that runs on userform_activate or userform_initialize and may possibly use a function to create date data. I would prefer to have this in VBA, but if it has to be in a worksheet for now until I figure something out that's fine.

Any help would be great, the more work I can make the workbook do the better it will be for other's that use it.

 

[dhosi439]

Here is the formula for the blue box,

For some reason I cannot copy and paste the code, it doesn't seem to like the < symbol.

 

[p45cal]

First, see if this formula:

=B2+((INT((TODAY()-B2-1)/14)+1)*14)

can replace the one below.
I'm still looking at your other request.

Here is the formula for the blue box,

For some reason I cannot copy and paste the code, it doesn't seem to like the < symbol.

 

[p45cal]

Sorry, I think that should be:

=B2+(INT((TODAY()-B2)/14)*14)

First, see if this formula:

=B2+((INT((TODAY()-B2-1)/14)+1)*14)

can replace the one below.
I'm still looking at your other request.

 

[p45cal]

Regarding the first pay day of the year(red box) it's curious that it's the first Thursday of the year.
How would you define the first pay day of ANY year, without referring to the previous or subsequent years?

 

[dhosi439]

Thanks for the much simpler formula for the blue box.

Just as a note, the first pay of 2016 through 2021/22 is the second week instead of the first week, then switches back to the first week of January after 2021/22, and repeats the pattern.

Okay, I will enter the first pay of the year manually, until I think of a work around.

I would like to recreate what this formula does in VBA, since I would prefer not to use any worksheet data for this.

Any thoughts?

 

[p45cal]

I would like to recreate what this formula does in VBA, since I would prefer not to use any worksheet data for this.

Any thoughts?

Well, I don't really see the point because the only cell referred to is the date in B2, however..

A vba function might be:

Function PayDue01()
FirstPayDate = DateValue("7 Jan 2010") 'hard coded value
'FirstPayDate = ThisWorkbook.Sheets("Sheet2").Range("B2").value 'the same from a sheet
PayDue01 = FirstPayDate + Int((Date - FirstPayDate) / 14) * 14
End Function

used in a spreadsheet thus:
=PayDue01()

or perhaps a function where you supply the first PayDate in a year:

Function PayDue02(FirstPayDate)
PayDue02 = FirstPayDate + Int((Date - FirstPayDate) / 14) * 14
End Function

used in a sheet thus:
=PayDue02(B2)
or
=PayDue02(DATEVALUE("7 Jan 2010"))