Convert period to date

[JerryB]

Stuck on an issue that I'm hoping someone can offer some help or point me in the right direction. I think VB is probably the easiest way to handle this, I didn't have much luck with a query.

I have 36 "periods", 2 each month on the 1st and 15th.

30k foot of that I need to do:

period 1_1 would be based off of todays date. So if today is the 3rd (less than the 15th) it would make period 1_1 = 4/1/22. If today was 4/15 thru 4/30 it would make period 1_1 = 4/15/2022
I would think it would be possible to now take period 1_1's date to figure out period 1_2: If period 1_1 = 4/15/2022 than period 1_2 would be 5/1/2022
and so on, down to period 18_2

period1_1 = if TODAY dd < 15 then dd=1 else dd=15
period1_2 = if period1_1 dd < 15 then dd=1 else dd=15
period2_1 = if period1_2 dd < 15 then dd=1 else dd=15
.......
period18_2 = if period18_1 dd < 15 then dd=1 else dd=15

Appreciate any pointers.

[SamT]

Compute all period Dates as Date

I like to use CDate(Format(...) to compute the first 2 dates
This is an algorithm, not Code. Assumes SecondPeriod is still in this year

FirstMonth = Month(Date)
SecondMonth = FirstMonth
FirstYear = Year(Date)
SecondYear = FirstYear

If Day(Date) < 15 then
FirstDay = 1
SecondDay = 15
Else
FirstDay = 15
SecondDay = 1
SecondMonth = FirstMonth + 1 + 1


FirstPeriod = CDate(Format(FirstMonth & "/" & FirstDay & "/" & FirstYear, "mm/dd/yy"))
SecondPeriod = CDate(Format(SecondMonth & "/" & SecondDay & "/" & SecondYear, "mm/dd/yy"))
)

Now use a Counter, a Loop, and DateAdd a month to each period. DateAdd returns a Date, Format Date as desired.
3rdPeriod = Format(DateAdd("m", Counter/Looper, Period), "mm/dd/yy") Etc

[arnelgp]

what happens if the month has 31 days, it will always get ignored.
also for feb, there is no 30, only 28 or 29.

you create a table tblPeriods (same as in the demo).
and create a query from your table (open query1).

[SamT]

30? 31?

DateAdd 1 month to the 15th of any month returns the 15th of the next month even if starting in January

[JerryB]

Thanks a bunch, got it figured out and working.