none
Setting Bi-Monthly Dates based on current date RRS feed

  • Question

  • I have a report that needs to run bi-monthly and I'd like to default the start and end date based on todays date.

    If todays date is between the 1st and the 15th, then the start and end date need to be the previous months 16th-last day of month.

    if todays date is between the 16th and last day of month, start and end date need to be 1st-15th of that month.

    Is there a graceful way to handle last day of month and having to go back a previous year?

    Thank you

    Wednesday, December 11, 2019 7:08 PM

All replies

  • Use:

    Between DateSerial(Year(d),Month(d)+(Day(d)<16),1-15*(Day(d)<16)) And DateSerial(Year(d),Month(d),-15*(Day(d)>15))

    This will handle year-end transition and leap years correctly.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, December 11, 2019 7:23 PM
  • Between DateSerial(Year(d),Month(d)+(Day(d)<16),1-15*(Day(d)<16)) And DateSerial(Year(d),Month(d),-15*(Day(d)>15))

    Hi,

    If your dates/datetimes contain a time component (a non zero fraction) then the BETWEEN-AND construction will loose the datetimes on the last day of the period!

    In those cases you MUST take (but in general you SHOULD better take) something like:

        (This_date >= Start_current_period) AND (This_date < Start_next_period)

    Imb.


    • Edited by Imb-hb Wednesday, December 11, 2019 9:28 PM
    Wednesday, December 11, 2019 9:27 PM