Consulting

Results 1 to 5 of 5

Thread: Convert period to date

  1. #1
    VBAX Newbie
    Joined
    Apr 2022
    Posts
    2
    Location

    Convert period to date

    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.

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    Last edited by SamT; 04-01-2022 at 08:45 AM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    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).
    Attached Files Attached Files

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    30? 31?

    DateAdd 1 month to the 15th of any month returns the 15th of the next month even if starting in January
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Newbie
    Joined
    Apr 2022
    Posts
    2
    Location
    Thanks a bunch, got it figured out and working.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •